Oh my love affair with Powershell gets overwhelming some days.  It really is a troublesome little mistress.

For every time somebody comes up to me and asks “Can this be done in Powershell?” the answer is almost invariably “YES!”

Sometimes the answer takes a bit of thought and bit of effort.

But what I love, is once you design the answer it is almost ALWAYS Neutral of your Infrastructure or Development Environment!  You can take those solutions with you anywhere!

Today we had a question.  “Can’t we just query the SQL servers to see what instances we have?”

I’m not an SQL guy.  I can’t even pretend to be.  I can install it, I can navigate it, I can drop tables and make messes.

But I’m not a SQL guru.

But I DO know that SQL server 2008 Management tools has a Powershell Snapin.  It sits INSIDE the Management Studio but it’s there.

It’s a very Dead simple command to for Powershell people.  Just use the SQL Provider and good old “GET-CHILDITEM”




Where “SERVERNAME” is the name of an SQL server.  And you can navigate to the different servers this way to by just changing the name.

Now the problem I ran into is if you don’t have credentials, you can’t connect (DUH).  I couldn’t find the answer yet on how to pass alternate credential in the SQLProvider in Powershell.  But I DID discover that the database list on a SQLServer (like a LOT of information in Windows) can be obtained by good old WMI.


So the alternate method you can ALSO use to show the Databases on a SQL Server is run a GET-WMIOBJECT against “win32-perfformatteddata-mssqlserver-sqlserverdatabases” (*Yes, it’s a mouthful*)


GET-WMIOBJECT win32-perfformatteddata-mssqlserver-sqlserverdatabases


Need the list from a foreign computer?  Just drop in the IP address or resolvable name!


GET-WMIOBJECT win32-perfformatteddata-mssqlserver-sqlserverdatabases –computer SERVERNAME


And of course like everything else in Powershell, passing credentials will validate you if necessary. 


GET-WMIOBJECT win32-perfformatteddata-mssqlserver-sqlserverdatabases –computer SERVERNAME –credential DOMAINUsername


Now you’d like that to be readable, because WMI usually gives a LOT of information we don’t need, so just format the output to a table and pick what you need.


GET-WMIOBJECT win32-perfformatteddata-mssqlserver-sqlserverdatabases –computer SERVERNAME –credential DOMAINUsername | format-table Name

See?  And I didn’t even trip on a single “Table” in the “process”.

Yes my worst Pun of the day.


Powershell, Enjoy the good life

The Energized Tech