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 (Exception ex)
        {
            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.

Answer 1

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.

Answer 2

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;
    }
}

Answer 3

I fully support Frederik Gheysels answer. If you have to support multiple database systems, you should implement your code against an abstract interface with specific implementations per database system. There are many more examples of incompatible syntax than just checking for an existing table (e.g.: limiting the query to a certain number of rows).

But if you really have to perform the check using the exception handling from your example, you should use the following query that is more efficient than a COUNT(*) because the database has no actual selection work to do:

SELECT 1 FROM my_table WHERE 1=2

Answer 4

In current project on my job I need to write 'data agent' which would support a lot of database types.

So I decided to do next: write a base class with the base (database independent) functionality using virtual methods and override in subclasses all database-specific moments

Answer 5

I would avoid executing the "select count(x) from xxxxxx" as the DBMS will actually go ahead and do it which may take some time for a large table.

Instead just prepare a "select * from mysterytable" query. The prepare will fail if mysterytable does not exist. There is no need to actually execute the prepared statement.

Answer 6

If you're trying for database independence you will have to assume a minimum standard. IIRC The ANSI INFORMATION_SCHEMA views are required for ODBC conformance, so you could query against them like:

select count (*) 
  from information_schema.tables 
 where table_name = 'foobar'

Given that you are using ODBC, you can also use various ODBC API calls to retrieve this metadata as well.

Bear in mind that portability equates to write-once test anywhere so you are still going to have to test the application on every platform you intend to support. This means that you are inherently limited to a finite number of possible database platforms as you only have so much resource for testing.

The upshot is that you need to find a lowest common denominator for your application (which is quite a lot harder than it looks for SQL) or build a platform-dependent section where the non-portable functions can be plugged in on a per-platform basis.