PowerShell.com
Library » Script Library » SQL Server » Get-MSSQL-DB-UsingADO

Get-MSSQL-DB-UsingADO

Previous | Next | View all files | View Slideshow
posted by Richard Giles
10-06-2008
Downloads: 607
File size: 2kB
Views: 2,221
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. ( 
  18.       [string]$serverInstance = ".", 
  19.     [switch]$verbose, 
  20.     [switch]$debug 
  21. ) 
  22.  
  23. function main() 
  24. { 
  25.     if ($verbose) {$VerbosePreference = "Continue"} 
  26.     if ($debug) {$DebugPreference = "Continue"} 
  27.     Get-MSSQL-DB-UsingADO $serverInstance 
  28. } 
  29.  
  30. function Get-MSSQL-DB-UsingADO($serverInstance) 
  31. { 
  32.     # TIP: using PowerShell to create an exception handler 
  33.    trap [Exception]  
  34.     { 
  35.       write-error $("TRAPPED: " + $_.Exception.Message); 
  36.       continue; 
  37.    } 
  38.      
  39.     $adoOpenStatic = 3 
  40.     $adoLockOptimistic = 3 
  41.      
  42.     # Create ADO connection and recordset objects     
  43.     $adoConnection = New-Object -comobject ADODB.Connection 
  44.     $adoRecordset = New-Object -comobject ADODB.Recordset 
  45.  
  46.     Write-Debug "Opening connection..." 
  47.     $adoConnection.Open("Provider=SQLOLEDB;Data Source=$serverInstance;Initial Catalog=master;Integrated Security=SSPI") 
  48.      
  49.     # Run query to retrieve database ids and names 
  50.     $query = "SELECT dbid, name FROM master.dbo.sysdatabases ORDER BY name" 
  51.     $adoRecordset.Open($query, $adoConnection, $adoOpenStatic, $adoLockOptimistic) 
  52.     $adoRecordset.MoveFirst() 
  53.      
  54.     Write-Debug "Retrieving results..." 
  55.  
  56.     do  
  57.     { 
  58.         $dbID = $adoRecordset.Fields.Item("dbid").Value 
  59.         $dbName = $adoRecordset.Fields.Item("name").Value 
  60.         Write-Output "$dbID : $dbName" 
  61.         $adoRecordset.MoveNext() 
  62.     } until ($adoRecordset.EOF -eq $TRUE) 
  63.      
  64.     $adoRecordset.Close() 
  65.     $adoConnection.Close() 
  66. } 
  67.  
  68. main 
Filed under: , ,

Show all SQL Server databases for a given server instance.

Copyright 2012 PowerShell.com. All rights reserved.