Query SQL Server with PowerShell

Another simple, yet very useful PowerShell function. I can’t count the times when I just needed to see some info in a SQL Database, but there was no SQL Management Studio. Often there are even restrictions on whether you can run EXE files, so tools like QueryExpress are not applicable too.

So here is a PowerShell function with which you can query whatever you like in a SQL Server Database if you have the rights of course. No dependencies other than .NET Framework.

You pass it a Connection String, SQL Query and the Columns you want to get and it returns an Array of PSCustom Objects.

Here’s how you call it. The Columns are comma separated if there are more than one.

$result = sqlReader -sqlConString "Server=SERVER;Database=DATABASE;Integrated Security=SSPI" -sqlComString "SELECT TOP 10 * FROM TableName" -columnsCommaDel "PERSONAL_ID,FIRST_NAME_BG"

Here is the function itself:

function sqlReader($sqlConString, $sqlComString, $columnsCommaDel){

    $sqlCon = new-object System.Data.SqlClient.SqlConnection($sqlConString)

    $sqlCon.open()

    $sqlCom = new-object System.Data.SqlClient.SQlCommand

    $sqlCom.CommandText = $sqlComString

    $sqlCom.CommandType = "Text"

    $sqlCom.Connection = $sqlCon

    $sqlReader = $sqlCom.ExecuteReader()

    $arrSQLResults=@()

    if($columnsCommaDel -like "*,*"){

            $columnsToGet = $columnsCommaDel.split(",")

    } else {

        $columnsToGet = $columnsCommaDel

    }

    while ($sqlReader.Read()){

        $obj = new-object psobject

        foreach($column in $columnsToGet){

            $obj | Add-Member -type NoteProperty -Name $column.trim() -Value $sqlReader[$column.trim()]

        }

        $arrSQLResults+=$obj

    }

    $sqlCon.close()

    return $arrSQLResults

}