September 14th, 2006

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

Hey, Scripting Guy! Question

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

— ER

SpacerHey, Scripting Guy! AnswerScript Center

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.

Note. Did we forget to call this a “well-deserved” vacation? No; forgetfulness had nothing to do with us not calling it that.

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.”

Note. True fact: the Scripting Guy who writes this column has never read a book about computers or technology, at least not all the way through. Well, provided you don’t count The Brave Little Toaster, of course.

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:

Parameter

Description

Provider

Essentially indicates the type of database we’re connecting to. Because we’re connecting to SQL Server, we use the SQLOLEDB provider, which is short for “Microsoft OLE DB Provider for SQL Server.”

And no, you don’t have to be a nerd to know what SQLOLEDB is short for.

Data Source

Denotes the computer we’re connecting to. Here we indicate the computer by using the variable strComputer.

Trusted_Connection

Indicates the user authentication mode. Setting this value to Yes causes us to connect uses Windows Authentication mode, which happens to be the recommended way of connecting to SQL Server.

Initial Catalog

Specifies the default catalog to use when making the connection. We use the Master catalog because that’s where we’ll find the list of databases.

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.

Note. Do we know that through personal experience, like, say, because we left that line out when we first sat down to write the script for today’s column? Well, we – um, sorry, but Microsoft doesn’t like us to talk about personal experiences in our columns ….

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.

Author

0 comments

Discussion are closed.