Get-MSSQL-DB-UsingADO


posted by Richard Giles
10-06-2008

Downloads: 685
File size: 2kB
Views: 2,612

Embed
Get-MSSQL-DB-UsingADO
  1. ## ===================================================================== 
  2. ## Title       : Get-MSSQL-DB-UsingADO 
  3. ## Description : Show all databases for a given server instance 
  4. ## Author      : Idera 
  5. ## Date        : 9/1/2008 
  6. ## Input       : -serverInstance <server\instance> 
  7. ##                   -verbose  
  8. ##                   -debug     
  9. ## Output      : List database ids and names 
  10. ## Usage            : PS> . Get-MSSQL-DB-UsingADO -serverInstance MyServer -v -d 
  11. ## Notes            : Adapted from Jakob Bindslet script 
  12. ## Tag            : SQL Server, SMO, List databases 
  13. ## Change log  : 
  14. ## ===================================================================== 
  15.  
  16. param 
  17.       [string]$serverInstance = "."
  18.     [switch]$verbose
  19.     [switch]$debug 
  20.  
  21. function main() 
  22.     if ($verbose) {$VerbosePreference = "Continue"
  23.     if ($debug) {$DebugPreference = "Continue"
  24.     Get-MSSQL-DB-UsingADO $serverInstance 
  25.  
  26. function Get-MSSQL-DB-UsingADO($serverInstance
  27.     # TIP: using PowerShell to create an exception handler 
  28.    trap [Exception]  
  29.     
  30.       write-error $("TRAPPED: " + $_.Exception.Message); 
  31.       continue
  32.    
  33.      
  34.     $adoOpenStatic =
  35.     $adoLockOptimistic =
  36.      
  37.     # Create ADO connection and recordset objects     
  38.     $adoConnection = New-Object -comobject ADODB.Connection 
  39.     $adoRecordset = New-Object -comobject ADODB.Recordset 
  40.  
  41.     Write-Debug "Opening connection..." 
  42.     $adoConnection.Open("Provider=SQLOLEDB;Data Source=$serverInstance;Initial Catalog=master;Integrated Security=SSPI"
  43.      
  44.     # Run query to retrieve database ids and names 
  45.     $query = "SELECT dbid, name FROM master.dbo.sysdatabases ORDER BY name" 
  46.     $adoRecordset.Open($query, $adoConnection, $adoOpenStatic, $adoLockOptimistic
  47.     $adoRecordset.MoveFirst() 
  48.      
  49.     Write-Debug "Retrieving results..." 
  50.  
  51.     do  
  52.     
  53.         $dbID = $adoRecordset.Fields.Item("dbid").Value 
  54.         $dbName = $adoRecordset.Fields.Item("name").Value 
  55.         Write-Output "$dbID : $dbName" 
  56.         $adoRecordset.MoveNext() 
  57.     } until ($adoRecordset.EOF -eq $TRUE
  58.      
  59.     $adoRecordset.Close() 
  60.     $adoConnection.Close() 
  61.  
  62. main 
Filed under: , ,

Show all SQL Server databases for a given server instance.

Copyright 2012 PowerShell.com. All rights reserved.