Returning sysdatabase Information from Multiple SQL Servers

• john monroe

  Old Hand

  Points: 365

  More actions

  June 21, 2007 at 9:34 pm

  #173111

  Is there a way to easily get this information using TSQL or do I have to create an Active X vbscript and run that as a job.  Thanks for your help in advance.

• Sugesh Kumar

  One Orange Chip

  Points: 27311

  More actions

  June 21, 2007 at 10:44 pm

  #714487

  Search this site for a vbs script that has been already posted that will give you a list of all user databases in an excel format for multiple servers/instances.

  Cheers,
  Sugeshkumar Rajendran
  SQL Server MVP
  http://sugeshkr.blogspot.com

• john monroe

  Old Hand

  Points: 365

  More actions

  June 21, 2007 at 10:48 pm

  #714490

  I found an example that works as a vbscript to run but I want to put this into a T-SQL statement.  There don't appear to be examples that do this. 

  Dim oDBConnect,sQuery,oRecordSet

  'WScript.Echo sServerName

  Set oDBConnect = CreateObject("ADODB.Connection")

  oDBConnect.Open "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=master;Data Source=" & sServerName

  sQuery = "select * from sysdatabases"

  Set oRecordSet  = oDBConnect.Execute(sQuery)

  oRecordSet.MoveFirst

  While Not oRecordSet.EOF

   wscript.echo oRecordSet("Name")

   oRecordSet.MoveNext

  Wend

  Set oRecordSet = Nothing

  Set oDBConnect = Nothing

• Jeff Moden

  SSC Guru

  Points: 1004614

  More actions

  June 21, 2007 at 11:35 pm

  #714503

  Start with OSQL -L

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)