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()

image

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

image

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

Conclusion:

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.

[UPDATE 2017-01-09]

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

Please Share This:

Share on facebook
Share on twitter
Share on linkedin

You may also like:

One Response

  1. Just an additional note – for those without SQL 2008, the SQLPSX package at http:sqlpsx.codeplex.com includes some commands that can do similar things such as Get-ISData.

    I agree that anything to avoid that mess in the first example is worth investigating. Much easier than trying to do it the old way.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Follow:

Subcribe to Blog Via Email

Enter your email address to subscribe to this blog and receive notifications of new posts by email.

What I'm Saying on Twitter

Subscribe via feedburner