Cmdlets, Functions and Snapins

So far we’ve learned how to open up PowerShell and add on to it with some modules; then we learned how to create a variable and populate it with anything from some numbers to an entire table.  Today we’re going to quickly look at another way of expanding PowerShell with something called a snapin so that we can get to some cmdlets.

First, what are cmdlets?  Cmdlets are similar to DOS commands but have some noteworthy differences.  In SQL Server terms you might think of cmdlets as the System Stored Procedures of PowerShell and think of functions as the regular user defined Stored Procedures of PowerShell. 

Cmdlets and functions accept parameters just like stored procedures do.  Just like stored procedures you can even define default values for these parameters in case the user doesn’t have one to pass in.  Unlike stored procedures though there is actually a drive that you can go to and see all of your functions.  Just run this and you’ll see what I mean dir function:

Snapins are akin to finished product code. They were created for PowerShell 1.0 and were succeeded in PowerShell 2.0 by modules– which are more like the manifest list for deploying a bunch of SSIS packages.  If you have SSMS 2008 installed you’ve got yourself two extra snapins already.  And if you have Idera’s SQL Diagnostic Manager installed you may be in for a real surprise when you retrieve your list of snapins.  Run this command to see all of the extra snapins that are available to you:
get-pssnapin –registered 

At the very least you should see this:

If you don’t see those two you’ll need to install SSMS 2008.  If you don’t have a copy of that handy you can get the free version of it here.  The SqlServerCmdletSnapin100 snapin give us the 5 cmdlets that come with SQL Server 2008 and today we’re going to focus on Invoke-SQLcmd which basically just calls out to good old sqlcmd.  To load this just run:

add-pssnapin SqlServerCmdletSnapin100

From there we can do a quick query like this:

invoke-sqlcmd -query "sp_databases" -database master -serverinstance WIN7Kilimanjaro | format-table

Now the reason that I brought up functions is because they override cmdlets and that’s really important to know.  If you run this command get-command invoke-sqlcmd you will only see one thing returned for invoke-sqlcmd.  However if we create a new function like:

function invoke-sqlcmd { get-date }

You end up overriding the Invoke-SQLcmd with your new function.  Give it a try, run just this invoke-sqlcmd and you should end up seeing something like this:

So how do you know when you’ve done something like that?  Just run get-command invoke-sqlcmd again and this time you will see this:

I realize that we kind of rushed through that info but thankfully there is only one more piece to the basics before we can get on to code that you can use on a daily basis