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.
To check if a table exists in SQL Server, you can use the INFORMATION_SCHEMA. TABLES table. You can use this table with an IF THEN clause do determine how your query responds whether or not a table exists.
Using the OBJECT_ID and the IF ELSE statement to check whether a table exists or not. Alternative 2 : Using the INFORMATION_SCHEMA. TABLES and SQL EXISTS Operator to check whether a table exists or not.
To check if table exists in a database you need to use a Select statement on the information schema TABLES or you can use the metadata function OBJECT_ID(). The INFORMATION_SCHEMA. TABLES returns one row for each table in the current database.
You can also check the data dictionary to see if a table exists: SQL> select table_name from user_tables where table_name='MYTABLE'; Another way to test if a table exists is to try to drop the table and catch the exception if it does not exist.
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 love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With