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”
Yup.
GET-CHILDITEM SQLSERVER:\SQL\SERVERNAME\DEFAULT\Databases
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 DOMAIN\Username
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 DOMAIN\Username | 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
Sean
The Energized Tech
Leave a comment