Using PowerShell and SMO to list Databases (and other stuff)

 

Using PowerShell and SMO to list Databases (and other stuff)

You don't have to use the new PowerShell Provider for SQL Server 2008 to talk to SQL Server, even for versions from 2000 up.

To do that, you'll need the Server Management Objects (SMO) libraries. You can install those separately, but you'll already have them if you have the client tools (Like SQL Server Management Studio) installed. You don't have to install anything on the server for this to work.

First, you'll need to load those libraries:

#Connect and run a command using SMO
[reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo")

Now you need to connect to your server (replace all the proper bits with your server names and so on here) - note that I'm using integrated authentication (this should all be on one line):

$sqlServer = new-object ("Microsoft.SqlServer.Management.Smo.Server") "servername\instancename"

Now you have a $sqlServer object you can play with. Here's a way to loop through all those and show the names (this should all be on one line):

foreach($sqlDatabase in $sqlServer.databases) {$sqlDatabase.name}

Do you see the .name part? You can show way more than that. To find out what you could put there instead, try this:

$sqlServer | get-member | more

Take a look at all of the items marked with "Property". Replace that .name part with one of those and you'll see all of the databases properties for that object.

As always, this warning applies to any script you find anywhere, including here.

Published 19 March 09 07:08 by Buck Woody

Carpe Datum : Using PowerShell and SMO to list Databases (and other stuff)