T-SQL Tuesday #39 – Can you shell what the PoSH is Cooking?
February 12, 2013 1 Comment
T-SQL Tuesday is a recurring blog party, that is started by Adam Machanic (Blog | @AdamMachanic). Each month a blog will host the party, and everyone that want’s to can write a blog about a specific subject.
A few months ago I attended a session of Jeff Wouters (Blog | @JeffWouters) about PowerShell. He talked about how powerful PowerShell really is, and showed us some examples. This was my first encounter with the “commandline on steroids”. Back then I didn’t think I’d use that any time soon, but this is changing fast! The wide variety of possibilities that PowerShell offers made me curious, but I never found the time and use for it. Until last week…
A great number of things have changed the last few weeks, and that gave me the opportunity to play around with PowerShell. Starting with reading a tutorial and creating my first few trial scripts, it soon hit me that it isn’t that easy. But on the other side, if you succeed at writing a script, it’s very powerful!
One of the very first things I tried after staring the PowerShell ISE (the “studio” in which you can write your scripts) was trying to find all objects in my database. The first thing you need to do is:
#Set the Execution-Policy to Unrestricted Set-ExecutionPolicy Unrestricted #Run the Import-Module below once, so you can use the SQLSERVER functionality Import-Module SQLPS
This will allow you to access your SQL Server objects. After that, you can start with the easy stuff like:
dir SQLSERVER:\sql\localhost\SQL2012\databases\Sandbox\Tables dir SQLSERVER:\sql\localhost\SQL2012\databases\Sandbox\Views dir SQLSERVER:\sql\localhost\SQL2012\databases\Sandbox\StoredProcedures dir SQLSERVER:sql\localhost\SQL2012\databases\Sandbox\Users
This is very basic, and real easy to find out how this works. From Powershell, the results will look like this:
And from the “PowerShell ISE”, it will look like this:
Another thing I tried is to automatically create a database on my local server:
[void] [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.ConnectionInfo") [void] [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") $serverInstance = "(local)\SQL2012" $conn = New-Object -TypeName Microsoft.SqlServer.Management.Common.ServerConnection -ArgumentList $serverInstance $server = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Server -ArgumentList $conn $newdb = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Database($server, "PowerShellToTheRescue") $newdb.Create()
This script will create a new database called “PowerShellToTheRescue” on your local SQL Server. I assume it’s also possible to create tables in the database, but I haven’t found the time to test that.
PowerShell trough SQL Server Management Studio (SSMS)
Another option is to start PowerShell via a menu in SSMS. This means PowerShell is starting with the current database as default context. So then you don’t have to use the “Import-Module”, and you can start running your query right away. You can find this option by right-clicking your database, and choose “Start PowerShell”:
So, what are you saying?!
This months T-SQL Tuesday post isn’t that impressive, I know! But what I’m trying to say is that if you try PowerShell, you might find that it’s quite powerful and fun.
And thinking about it, I guess you can expect some more PowerShell posts from me in the next few weeks!😉