检查是否存在 SQL 表 [英] Check if a SQL table exists

问题描述

以数据库独立方式检查表是否存在于 Sql 数据库中的最佳方法是什么?

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

我想出了:

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

有没有更好的方法来做到这一点?当与数据库的连接失败时，此方法将不起作用.我找到了适用于 Sybase、SQL server、Oracle 的方法，但没有任何方法适用于所有数据库.

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.

推荐答案

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

这篇关于检查是否存在 SQL 表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！