SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Basic SQL Querying From PowerShell

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”)
$cmd1 = $conn.CreateCommand()
$cmd1.CommandType = [System.Data.CommandType]::StoredProcedure
$cmd1.CommandText =“sp_databases”
$data = $cmd1.ExecuteReader()
$dt = new-object “System.Data.DataTable”
$dt | format-table #PipeLining is Awesome!


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



Unless you really like writing 6 or 7 extra lines of code every time you query your database, load up the Snapin and use invoke-sqlcmd.


Aaron Nelson ( blog | twitter ) is a Microsoft MVP for SQL Server (Data Platform) and leads the PowerShell Virtual Chapters of PASS, and volunteers for the local PASS Chapter AtlantaMDF, and helps organize SQL Saturday events in Atlanta. The PowerShell VC of PASS hosts monthly sessions on SQL Server and PowerShell, and you can find the recordings of those sessions on their YouTube channel.


Posted by patelv61 on 27 September 2016

First example  did not work  but second one is just work corectly

Leave a Comment

Please register or log in to leave a comment.