How Can I Get a List of All the Databases on a SQL Server Computer?

ScriptingGuy1

Hey, Scripting Guy! How can I get a list of all the databases on a SQL Server computer?

— ER

Hey, ER. Before we answer your question, we’d like to take a moment to offer our opinions on all the major – and controversial – issues of the day. We can do that because the Scripting Editor is on vacation and, well, you know what they say: when the cat’s away, the mice will play.

To tell you the truth, having the opportunity to show off the depth and breadth of his knowledge is important to the Scripting Guy who writes this column. That’s because he has long been typecast as some sort of nerd who cares only about computers. For example, when he worked at the University of Washington he was on his way out for the day when he was asked to stop by the Dean’s office and fix a computer. To do so he set down his book – a collection of short stories by P.G. Wodehouse – on a nearby table.

“P.G. Wodehouse! I just love P.G. Wodehouse!” declared the administrator when she saw the book lying there. “Whose book is this?”

“Mine,” said the future Scripting Guy.

“No, really, whose book is this?” she said. “I’d love to talk to them about it.”

“It’s mine,” insisted the Scripting Guy.

“Never mind,” she said. “I’ll find out later whose book it is.”

As she later explained, “I had no idea you’d ever read anything other than computer books.”

But now, at long last, the completely un-nerdish Scripting Guy who writes this column has a chance to show off his knowledge of current events. That’s right, current events. All the important things going on in the world today. Important things he knows all about. Yes. All his knowledge and wisdom, all those things he’s been dying to say but has never been allowed to.

Right ….

So, um, that Dred Scott decision of 1857: what was up with that?

You know what? When the Scripting Editor gets back she’ll no doubt go through and delete all these insightful — albeit controversial — opinions anyway. It wouldn’t be very nice to have her to come back from vacation and then have to do all that work, doesn’t? Therefore, solely in the spirit of making life easier for the Scripting Editor, let’s just show you a script that can list all the databases on a computer running Microsoft SQL Server:

strComputer = “atl-ds-01” 

Set objConnection = CreateObject(“ADODB.Connection”)
objConnection.Open _
    “Provider=SQLOLEDB;Data Source=” & strComputer & “;” & _
        “Trusted_Connection=Yes;Initial Catalog=Master”
Set objRecordset = objConnection.Execute(“Select Name From SysDatabases”)
If objRecordset.Recordcount = 0 Then
    Wscript.Echo “No databases could be found.”
Else
    Do Until objRecordset.EOF
        Wscript.Echo objRecordset.Fields(“Name”)
        objRecordset.MoveNext
    Loop
End If

So how does this script work? How the heck would we know? What do we look like, a bunch of nerds?

Good point; we probably should get rid of pocket protectors, shouldn’t we? OK, well, in that case, the script starts out by assigning the name of the target computer to a variable named strComputer. We then use this line of code to create an instance of the ADODB.Connection object, the object we need to connect to the SQL Server service on the target computer:

Set objConnection = CreateObject(“ADODB.Connection”)

As soon we have a Connection object we can then call the Open method to actually connect to SQL Server:

objConnection.Open _
    “Provider=SQLOLEDB;Data Source=” & strComputer & “;” & _
        “Trusted_Connection=Yes;Initial Catalog=Master”

We won’t explain SQL Server connections and connection strings in any detail today; however, we will note that we pass the Open method several parameters:

After opening the Master catalog we can then use the Execute command to return a recordset consisting of all the databases found on this SQL Server machine. To do that we execute the following query, which simply selects the Name of each item (that is, each database) stored in the table SysDatabases:

Set objRecordset = objConnection.Execute(“Select Name From ysDatabases”)

So what do we do with that recordset once we have it? Well, to begin with, we check the value of the RecordCount which tells us how many items are in the recordset. If the RecordCount is 0, then there aren’t any databases on this computer; in turn, we echo back a statement to that effect.

Of course, the odds are pretty good that the RecordCount won’t be 0, meaning the computer has at least one database. If that’s the case, we then set up a Do Until loop that loops through the entire collection of records (or, as we nerds would put it, until the recordset’s EOF property – the End of File property – is True). Inside that loop we do two things: we echo back the value of the each record’s Name field, and we use the MoveNext method to advance from one record to the next:

Wscript.Echo objRecordset.Fields(“Name”)
objRecordset.MoveNext

Incidentally, don’t forget the MoveNext; if you do, your script will simply echo back the name of the first database in the collection, and then continue to echo that one name forever and ever. That’s because, with databases, you have to explicitly tell them to move on to the next record.

And what do we get when we’re all done? Why, we get a list of all the databases on the computer, of course:

master
tempdb
model
msdb
pubs
Northwind
CustomerScripts
CommunityScripts

Which is all we ever really wanted to get.

Now, tell us the truth, ER: do you think the Scripting Guy who writes this column is a nerd? (Note: No fair asking the Scripting Son for his opinion of dear old dad.) No, really, we want to know your honest opinion.

Yeah, well, who asked you anyway?

But it really was his book. Really.

ScriptingGuy1

Follow