Basic SQL Querying From PowerShell
[Update at bottom: 2017-01-09 with Best Option of using the new SqlServer module]
Also, check out my new MSSQLTip for a more in-depth walk through on this topic: https://www.mssqltips.com/sqlservertip/4678/powershell-invokesqlcmd-outputs-datatables-you-can-insert-into-sql-server/
I promise to make this worth your time. (Oops, I better make this quick.)
|Alright so with PowerShell if you open it up and just run this code you’ll be able to query your database right from the start. Anyone who has worked with data systems for a while will notice right away the first line of this script kinda looks like a connection string for an application. I’m sure the fact that PowerShell was built on .Net has something to do with that. (But I’m not a developer so I don’t really know, it could be just a coincidence for all I know.|
$conn = New-Object System.Data.SqlClient.SqlConnection(“Data Source=YourMachineName\YourInstanceName; InitialCatalog=master; Integrated Security=SSPI”) $conn.Open() $cmd1 = $conn.CreateCommand() $cmd1.CommandType = [System.Data.CommandType]::StoredProcedure $cmd1.CommandText =“sp_databases” $data = $cmd1.ExecuteReader() $dt = new-object “System.Data.DataTable” $dt.Load($data) $dt | format-table #PipeLining is Awesome! $conn.Close()
But this code is so much Nicer!
|If you’ve got SSMS 2008 installed on your machine all you need to do is fire up the ISE and add the SnapIn like I talked about in my last blog. Then you can use invoke-sqlcmd and pass it your query. As you see this is a lot easier and cleaner than all of the code above was.|
#Go ahead and add the SQL Snapins add-pssnapin SqlServerCmdletSnapin100 #sp_databases | format-table invoke-sqlcmd -query "sp_databases" -database master -serverinstance WIN7\Kilimanjaro | format-table
SSMS 2016 Makes It Even Better:
<# Go ahead and add the SqlServer Module #> Import-Module SqlServer <# sp_databases | format-table #> Invoke-Sqlcmd -Query "sp_databases" -Database master -ServerInstance localhost\SQL2016 | Format-Table
Unless you really like writing 6 or 7 extra lines of code every time you query your database, load up the
Snapin SqlServer module and use invoke-sqlcmd.
Starting with SQL Server 2012 you could use Import-Module SQLPS to get to the Invoke-SqlCmd
If you have SSMS 2016 v16.3+ (download the latest version here sqlps.io/dl ) you should do Import-Module SQLServer to get a much improved version of Invoke-SqlCmd which now includes outputting your results as .Net DataTables; which is important if you want to use the Write-SqlTableData cmdlet to inset those rows into another database (possible on a completely different instance).
To see an example of this in action, have a look at this post I wrote: Searching Multiple SQL Servers & Saving Results, with Write-SqlTableData