Check if a SQL table exists


Question

What's the best way to check if a table exists in a Sql database in a database independant way?

I came up with:

   bool exists;
   const string sqlStatement = @"SELECT COUNT(*) FROM my_table";

   try
    {
       using (OdbcCommand cmd = new OdbcCommand(sqlStatement, myOdbcConnection))
       {
            cmd.ExecuteScalar();
            exists = true;
       }
    }
    catch
    {
        exists = false;
    }

Is there a better way to do this? This method will not work when the connection to the database fails. I've found ways for Sybase, SQL server, Oracle but nothing that works for all databases.

1
39
5/19/2011 11:54:56 AM

Accepted Answer

bool exists;

try
{
    // ANSI SQL way.  Works in PostgreSQL, MSSQL, MySQL.  
    var cmd = new OdbcCommand(
      "select case when exists((select * from information_schema.tables where table_name = '" + tableName + "')) then 1 else 0 end");

    exists = (int)cmd.ExecuteScalar() == 1;
}
catch
{
    try
    {
        // Other RDBMS.  Graceful degradation
        exists = true;
        var cmdOthers = new OdbcCommand("select 1 from " + tableName + " where 1 = 0");
        cmdOthers.ExecuteNonQuery();
    }
    catch
    {
        exists = false;
    }
}
64
6/11/2010 6:44:55 AM

I don't think that there exists one generic way that works for all Databases, since this is something very specific that depends on how the DB is built.

But, why do you want to do this using a specific query ? Can't you abstract the implementation away from what you want to do ? I mean: why not create a generic interface, which has among others, a method called 'TableExists( string tablename )' for instance. Then, for each DBMS that you want to support , you create a class which implements this interface, and in the TableExists method, you write specific logic for this DBMS.
The SQLServer implementation will then contain a query which queries sysobjects.

In your application, you can have a factory class which creates the correct implementation for a given context, and then you just call the TableExists method.

For instance:

IMyInterface foo = MyFactory.CreateMyInterface (SupportedDbms.SqlServer);

if( foo.TableExists ("mytable") )
...

I think this is how I should do it.


Licensed under: CC-BY-SA with attribution
Not affiliated with: Stack Overflow
Icon