Check if a SQL table exists

0 votes
asked Jan 21, 2009 by carra

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.

8 Answers

0 votes
answered Jan 21, 2009 by frederik-gheysels

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.

0 votes
answered Jan 21, 2009 by michael-buen
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;
    }
}
0 votes
answered Jan 21, 2009 by sebastian-dietz

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
0 votes
answered Jan 21, 2009 by abatishchev

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

0 votes
answered Jan 21, 2009 by james-anderson

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.

0 votes
answered Jan 21, 2009 by concernedoftunbridge

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.

0 votes
answered Jan 14, 2016 by arvo-bowen

The following works well for me...

private bool TableExists(SqlConnection conn, string database, string name)
{
    string strCmd = null;
    SqlCommand sqlCmd = null;

    try
    {
        strCmd = "select case when exists((select '['+SCHEMA_NAME(schema_id)+'].['+name+']' As name FROM [" + database + "].sys.tables WHERE name = '" + name + "')) then 1 else 0 end";
        sqlCmd = new SqlCommand(strCmd, conn);

        return (int)sqlCmd.ExecuteScalar() == 1;
    }
    catch { return false; }
}
0 votes
answered Sep 15, 2017 by andreas-johansson

If you want to avoid try-catch solutions, I'm suggesting this method, using sys.tables

private bool IsTableExisting(string table)
    {
        string command = $"select * from sys.tables";
        using (SqlConnection con = new SqlConnection(Constr))
        using (SqlCommand com = new SqlCommand(command, con))
        {
            SqlDataReader reader = com.ExecuteReader();
            while (reader.Read())
            {
                if (reader.GetString(0).ToLower() == table.ToLower())
                    return true;
            }
            reader.Close();
        }
        return false;
    }
Welcome to Q&A, where you can ask questions and receive answers from other members of the community.
Website Online Counter

...