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.