Check if a SQL table exists

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

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.

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.