Thursday, November 30, 2006

PowerShell, SQL & ADO

The following function connects to a SQL server through ADO, and queries the master database for a list of the database id (dbid) and name of all databases on the server:

function ShowAllDBs {
$adOpenStatic = 3
$adLockOptimistic = 3
     $objConnection = New-Object -comobject ADODB.Connection
     $objRecordset = New-Object -comobject ADODB.Recordset
     $query = "SELECT dbid, name FROM master.dbo.sysdatabases ORDER BY dbid"
     $objConnection.Open("Provider=SQLOLEDB; Data Source=" + $args[0] + "; Initial Catalog=master; Integrated Security=SSPI")
     $objRecordset.Open($query, $objConnection, $adOpenStatic, $adLockOptimistic)
     $objRecordset.MoveFirst()
     do {
          $temp0 = $objRecordset.Fields.Item("dbid").Value
          $temp1 = $objRecordset.Fields.Item("name").Value
          Write-Output "$temp0 : $temp1"
          $objRecordset.MoveNext()
     } until     ($objRecordset.EOF -eq $True)
     $objRecordset.Close()
     $objConnection.Close()
}

To use the function:

ShowAllDBs [server or server\instance]

2 comments:

Anonymous said...

Thanks for the code, I am using it in a production enviroment to dump database tables.

Jose A. Valdes Aceves said...

Hi,
Nice script, I'm trying to get the instances of each SQL server in a list. Do you know how to get them?