I needed to query a database table with PowerShell and manipulate the results, preferably in a data table. I found querying this information was possible, but the results would be a static list and not very usable. I also did not wish to install SQL components to utilize other SQL Snapins, since I could use scripts on many different computers.
Thus, this function was created.
Example:
Example of select specific columns and search criteria:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 |
# FUNCTION to connect to an MS SQL Server and select a database table. # Created by Forrest McFaddin | 2017/9/27 # You are able to specify Server, Database, Table, Columns, and search criteria. # Values are returned as a table for future use. function Get-SQLTable{ param( [Parameter(Mandatory=$true)][string]$SQL_Server, [Parameter(Mandatory=$true)][string]$SQL_Database, [Parameter(Mandatory=$true)][string]$SQL_Table, [Parameter(Mandatory=$false)][string]$SQL_Table_Columns, [Parameter(Mandatory=$false)][string]$Criteria ) # Build Connection String $SQL_Connection = ("Server=$SQL_Server;Database=$SQL_Database;Integrated Security=True") $SQL_Cmd_Table = ($SQL_Table) # Build SQL object for command and connection $Sql_Cmd = New-Object System.Data.SqlClient.SqlCommand $Sql_Cmd.Connection=New-Object System.Data.SqlClient.SqlConnection $Sql_Cmd.Connection.ConnectionString=$SQL_Connection $Sql_Cmd.Connection.Open() ### Evaluate variables provided ### IF(!$Criteria -AND !$SQL_Table_Columns){ # IF no columns and criteria specified, select all from table. $Sql_Cmd.CommandText="SELECT * FROM $SQL_Cmd_Table" }ELSEIF(!$Criteria -AND $SQL_Table_Columns){ # IF only columns specified, select all records for those columns. $Sql_Cmd.CommandText="SELECT $SQL_Table_Columns FROM $SQL_Cmd_Table" }ELSEIF($Criteria -AND !$SQL_Table_Columns){ # IF only criteria is set, but no columns, select all columns for the criteria. $Sql_Cmd.CommandText="SELECT * FROM $SQL_Cmd_Table WHERE $Criteria" }ELSEIF($Criteria -AND $SQL_Table_Columns){ # IF both columns and criteria are specified, select accordingly. $Sql_Cmd.CommandText="SELECT $SQL_Table_Columns FROM $SQL_Cmd_Table WHERE $Criteria" } # Set command to SELECT $adapter = New-Object System.Data.SqlClient.SqlDataAdapter $adapter.SelectCommand = $Sql_Cmd $dataset = New-Object System.Data.DataSet $adapter.Fill($dataset) # Fill the dataset # Grab values from dataset $table = $dataset.tables[0] $table_columns = $table.columns.columnName # Create final data table $MasterTable = New-Object System.Data.DataTable foreach($i in $table_columns){ $Col = New-Object System.Data.DataColumn($i) $MasterTable.Columns.Add($Col) } ### Populate values into columns ### # Set Current Line $Table_Current_Line = 0 # Set how many lines are in the table $Table_Line_Count = ($Table.rows.count) # Go through all lines, add master table row and populate values while($Table_Current_Line -lt $Table_Line_Count){ # Set row number $Current_Row = $table.rows[$Table_Current_Line] # Add MasterTable row $MasterTable_Row = $MasterTable.NewRow() # For each column of the table, get the values foreach($column_value in $table_columns){ # Set current value variable to populate table column $Current_Value = $Current_Row.$column_value # Add table column to row $MasterTable_Row[$column_Value]=$Current_Value } # END FOREACH # Add the row $MasterTable.rows.Add($MasterTable_Row) # Increcement to the next row in raw table $Table_Current_Line++ } # END WHILE $Sql_Cmd.Connection.Close() IF($MasterTable.rows.count -gt 0){ return $MasterTable }ELSE{ return } #End IF } # End Function |
Download this script.
Alternatively you can save all of the SQL results into a PowerShell variable and then specify a WHERE clause: