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.
I spoke in 
) I used the same 
We had a great crowd of about 60 people last night at the Atlanta MDF which was really great considering it was the first day of school for a lot of the parents in the area. The crowd had a bunch of great questions and while I couldn’t handle all of them I took several notes and will hopefully blog about the answers as I find them over the next couple of weeks.
and of course I can’t finish without saying: If you need some help getting started from scratch using PowerShell with SQL Server check out my