Quick Blog: What’s Missing From SQLPS

Last week Norm Kelm ( blog | twitter ) asked me on twitter ‘What cmdlets/features are missing from SQLPS.exe? Get-Snapin, etc. Where’s a doc that explains?’   To get the cmdlet half of this answer is somewhat easy in PowerShell using Compare-Object but it made me realize this is one of the many PowerShell tips that I have failed to blog about yet!  I plan to fix that next year with a deluge of blog posts but why wait until next year right?

To get a very fast answer to this answer I opened up PowerShell.exe because while I normally demo in PowerShell_ISE.exe, I don’t use a profile in PowerShell.exe (more on that in a later post).  I ran this command to get the count of just the cmdlets.

(get-command -CommandType Cmdlet).count

Then I fired up SQLPS.exe (typically found in C:\Program Files (x86)\Microsoft SQL Server\100\Tools\Binn) and ran the same command (after switching out of SQL Server and over to the C drive; no idea why that was necessary).

The only problem here is that it’s not really 236 – 130 = 106 because SQLPS.exe adds 5 cmdlets that I made sure to exclude (along with everything else I normally use) from PowerShell.exe. 

To get a more accurate list of the cmdlets and which one has what I decided to write a script that everyone con run on their own machine.  One of the many cool features of PowerShell is that you call call it’s executable and pass it a -command or even a –file.  I went ahead and threw together this simple command below so that you can see the differences yourself.

Note: I assume you are running Windows 7 which comes with PowerShell 2.0

$SQLPS=SQLPS.exe -command "CD C:\; get-command -CommandType Cmdlet | select CommandType, Name; exit";
$PSTwo=powershell.exe -command "get-command -CommandType Cmdlet | select CommandType, Name; exit";
Compare-Object -ReferenceObject $SQLPS -DifferenceObject $PSTwo|
Export-CSV C:\temp\SQLPSCommandDifferences.csv -NoTypeInformation

Now all that you have to do is open up that csv file that was just created (C:\temp\SQLPSCommandDifferences.csv) and have a look. 

Note: I could have just displayed the info inside of my PowerShell window by leaving off the last line and the pipe but then I would have needed to do something to format the output so I went with the CSV file instead.

For more information on this topic be sure to check out Chad Miller’s post on it.