Hey, Scripting Guy! How can I tell whether a specified table exists in a SQL Server database?

— LL

Hey, LL. Well, as it turns out it’s absolutely impossible to tell whether a specified table exists in a SQL Server database. So, sorry, time to go back to watching the Major League Baseball playoffs. By the way, who else out there hates the Dodgers? Don’t be shy; raise your hands.

Hmmm …. OK, if you say so. Um, we’ve just been informed of two things. First, Microsoft is paying us to answer scripting questions, not to watch the Major League Baseball playoffs. Second, those of us here at Microsoft don’t hate the Dodgers; in fact, we love everyone and everything.

Well, OK, maybe not the Oakland Raiders. But everyone else.

To tell you the truth, answering scripting questions isn’t quite as much fun as watching baseball. (It’s close, but ….) On top of that, if you saw our last paycheck you might not think that Microsoft is paying us at all, let alone paying us to answer scripting questions. Still, duty calls, right? With that in mind, here’s a script that can tell you whether a database named Fabrikam (located on the computer atl-sql-01) contains a table named EmployeeData:

Const adOpenStatic = 3
Const adLockOptimistic = 3

Set objConnection = CreateObject(“ADODB.Connection”)
Set objRecordSet = CreateObject(“ADODB.Recordset”)
objConnection.Open _
    “Provider=SQLOLEDB;Data Source=atl-sql-01;” & _
        “Trusted_Connection=Yes;Initial Catalog=Fabrikam”
objRecordSet.Open “SELECT * FROM sysobjects Where Name= ‘EmployeeData’ AND xType= ‘U'”, _
        objConnection, adOpenStatic, adLockOptimistic
If objRecordset.RecordCount = 0 Then
    Wscript.Echo “The table is not in the database.”
Else
    Wscript.Echo “The table is in the database.” 
End If

Before we begin we should note that we won’t be able to explain all the intricacies of connecting to databases in today’s column; that’s a bit more than we have room for. (Or time for: the Mets-Dodgers game starts in less than 20 minutes.) If you’re new to writing database scripts then we encourage you to check out our on-demand webcast Database Scripting for System Administrators. (OK, not one of our more inspired titles. But we were probably distracted by a baseball game or something at the time.)

As for our sample script, we start out by defining a pair of constants, adOpenStatic and adLockOptimistic; we’ll use this constants to assign the cursor and lock types when retrieving records from the database. (Like we said, take a peek at the webcast if you aren’t familiar with terms like cursor and lock types.) After creating instances of the ADODB.Connection and ADODB.Recordset objects we then use this line of code to connect to SQL Server on the computer atl-sql-01:

objConnection.Open _
    “Provider=SQLOLEDB;Data Source=atl-sql-01;” & _
        “Trusted_Connection=Yes;Initial Catalog=Fabrikam”

And yes, as a matter of fact it is easy to modify this script for your own use. Just replace atl-sql-01 with the name of the SQL Server machine you want to connect to, then replace Fabrikam with the name of the database you want to check. (Don’t be confused by the label Initial Catalog; we’re really just talking about a database here.)

What do you know: Nick Swisher just led off the top of the ninth with a double.

Uh, or so we heard someone mention as they walked by. Too bad those people aren’t busy working like the rest of us.

After we’ve made the connection to the SQL Server computer we then issue the following command in order to return a recordset consisting of all the tables in the Fabrikam database that have the name EmployeeData:

objRecordSet.Open “SELECT * FROM sysobjects Where Name= ‘EmployeeData’ AND xType= ‘U'”, _
        objConnection, adOpenStatic, adLockOptimistic

What do you mean you don’t see how we did that? Why it’s as plain as the nose on your – oh, maybe it’s not so plain after all, is it? Well, let’s see what we can do to help clear things up. To begin with, all SQL Server databases have a table named sysobjects. Sysobjects has one function: it keeps track of all the objects (tables, logs, constraints, stored procedures, etc.) that have been created within the database. As you can probably guess, that’s why we query the sysobjects table: if anyone knows whether or not a table exists sysobjects does. We then include the Where clause Where Name = ‘EmployeeData’ for obvious reasons: that’s the name of the table we’re interested in.

That leaves us with this additional clause:

AND xType= ‘U’

As it turns out, sysobjects includes a field named xType. This field is used to keep track of the object type; a user table has an xType of U (system tables have an xType of S). This AND clause leaves us with a query that says, “Bring me back a collection of all the objects in the database that not only have the Name EmployeeData but also (AND) happen to be a user table.”

To determine whether or not the database really does contain a user table named EmployeeData all we have to do is take a look at the recordset’s RecordCount property, a property which – logically enough – tells us how many records are in the recordset. RecordCount can be only one of two values: 0, meaning no such tables exist; or 1, meaning that the EmployeeData table does exist. (The RecordCount can never be greater than 1 simply because table names must be unique. That means you can only have, at most, one table named EmployeeData.) In this block of code we examine the value of the RecordCount property and then echo back the appropriate message:

If objRecordset.RecordCount = 0 Then
    Wscript.Echo “The table is not in the database.”
Else
    Wscript.Echo “The table is in the database.” 
End If

Whoa, 5-2 Oakland, bottom of the ninth. That is, for those of you interested in that sort of thing.

(Editor’s Note: Update, Oakland wins 5-2. Go Dodgers.)