Quick Blog: What’s Missing From SQLPS

imageLast 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.

Please Share This:

You may also like:

2 Responses

Leave a Reply

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


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