My musings about .NET and what not

Check if a Table Exists in a SQL Server Database

Need to check if a table exists in your SQL Server database before you do something with it? Here's a quick way to do that in both C# and VB.NET.

As I scramble to finish up my latest Wrox Blox e-Book on exception handling in ASP.NET, I haven't had much time to blog. However, I thought I'd toss up a neat little code snippet I came up with in the course of writing code for said book. (Necessity really is, as they say, the mother of invention.)

Part of the functionality I'm presenting is logging exceptions to a database. Of course, this requires the site administrator to set up the required table ahead of time. Then, I thought it might be cool to have a routine that checks if the table has been set up when an unhandled exception occurs, and then create it automatically if it doesn't exist.

Anyway, here's a little helper method I came up with to check if the table exists in the database:

static bool TableExists(string tableNameAndSchema)
{
   using (SqlConnection connection = new SqlConnection(connectionString))
   {
      string checkTable =
         String.Format(
            "IF OBJECT_ID('{0}', 'U') IS NOT NULL SELECT 'true' ELSE SELECT 'false'",
            tableNameAndSchema);
 
      SqlCommand command = new SqlCommand(checkTable, connection);
      command.CommandType = CommandType.Text;
      connection.Open();
 
      return Convert.ToBoolean(command.ExecuteScalar());
   }
}

And here is the equivalent in VB.NET:

Shared Function TableExists(tableNameAndSchema As String) As Boolean
   Using connection As New SqlConnection(connectionString)
      Dim checkTable As String = [String].Format("IF OBJECT_ID('{0}', 'U') IS NOT NULL SELECT 'true' ELSE SELECT 'false'", tableNameAndSchema)
      
      Dim command As New SqlCommand(checkTable, connection)
      command.CommandType = CommandType.Text
      connection.Open()
 
      Return Convert.ToBoolean(command.ExecuteScalar())
   End Using
End Function

All you need to do is to pass in the name of the table as an argument, like "myschema.MyTable", where myschema is the schema the table belongs to (this would probably be dbo in most cases) and MyTable is the name of your table. If a table with that name exists in the database pointed to by connectionString, then the method returns true, otherwise false.

I'm still not sure if I'll be incorporating this logic into my final book version or not. But still, this is a good one I'll be tucking away for future use. Hope this helps someone out!

Subscribe to this blog for more cool content like this!

kick it on DotNetKicks.com

shout it on DotNetShoutOut.com

vote it on WebDevVote.com

Bookmark / Share

    » Similar Posts

    1. Should We Return Null From Our Methods?
    2. Defensive Programming, or Why Exception Handling Is Like Car Insurance
    3. Building a TweetThis Extension with Automatic Bit.ly for Graffiti CMS

    » Trackbacks & Pingbacks

    No trackbacks yet.
    Trackback link for this post:
    http://leedumond.com/trackback.ashx?id=46

    » Comments

    1. Sanjit avatar

      Thanks! :)

      Helper was helpful. :)

      Sanjit — March 18, 2010 6:14 AM

    » Leave a Comment