C# 检查 SQL 表是否存在
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/464474/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Check if a SQL table exists
提问by Carra
What's the best way to check if a table exists in a Sql database in a database independant way?
以独立于数据库的方式检查 Sql 数据库中是否存在表的最佳方法是什么？
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.
有一个更好的方法吗？当与数据库的连接失败时，此方法将不起作用。我找到了适用于 Sybase、SQL 服务器、Oracle 的方法，但没有任何方法适用于所有数据库。
采纳答案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;
}
}
回答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.
但是，为什么要使用特定查询来执行此操作？你不能把实现从你想做的事情中抽象出来吗？我的意思是：为什么不创建一个通用接口，其中包括一个名为“TableExists(string tablename)”的方法。然后，对于您想要支持的每个 DBMS，您创建一个实现此接口的类，并在 TableExists 方法中，为该 DBMS 编写特定的逻辑。
然后，SQLServer 实现将包含一个查询 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.
在您的应用程序中，您可以拥有一个为给定上下文创建正确实现的工厂类，然后您只需调用 TableExists 方法。
For instance:
例如：
IMyInterface foo = MyFactory.CreateMyInterface (SupportedDbms.SqlServer);
if( foo.TableExists ("mytable") )
...
I think this is how I should do it.
我想这就是我应该做的。
回答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).
我完全支持 Frederik Gheysels 的回答。如果您必须支持多个数据库系统，您应该针对每个数据库系统具有特定实现的抽象接口来实现您的代码。除了检查现有表（例如：将查询限制为一定数量的行）之外，还有更多不兼容语法的示例。
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:
但是，如果您真的必须使用示例中的异常处理来执行检查，您应该使用以下比 COUNT(*) 更有效的查询，因为数据库没有实际的选择工作要做：
SELECT 1 FROM my_table WHERE 1=2
回答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
所以我决定下一步：使用虚拟方法编写一个具有基本（独立于数据库）功能的基类，并在子类中覆盖所有特定于数据库的时刻
回答by James Anderson
I would avoid executing the select count(x) from xxxxxxas the DBMS will actually go ahead and do it which may take some time for a large table.
我会避免执行，select count(x) from xxxxxx因为 DBMS 实际上会继续执行，这对于大表可能需要一些时间。
Instead just preparea select * from mysterytablequery. The prepare will fail if mysterytable does not exist. There is no need to actually execute the prepared statement.
而只是准备一个select * from mysterytable查询。如果mysterytable 不存在，准备将失败。不需要实际执行准备好的语句。
回答by ConcernedOfTunbridgeWells
If you're trying for database independence you will have to assume a minimum standard. IIRC The ANSI INFORMATION_SCHEMAviews are required for ODBC conformance, so you could query against them like:
如果您正在尝试数据库独立性，您将不得不假设一个最低标准。IIRC ANSI INFORMATION_SCHEMA视图是 ODBC 一致性所必需的，因此您可以像这样查询它们：
select count (*)
from information_schema.tables
where table_name = 'foobar'
Given that you are using ODBC, you can also use various ODBC API callsto retrieve this metadata as well.
鉴于您使用的是 ODBC，您还可以使用各种ODBC API 调用来检索此元数据。
Bear in mind that portability equates to write-once test anywhereso 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.
结果是您需要为您的应用程序找到一个最小的公分母（这比查找 SQL 困难得多）或者构建一个平台相关的部分，其中可以在每个平台上插入不可移植的函数基础。
回答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; }
}
回答by Andreas Johansson
If you want to avoid try-catch solutions, I'm suggesting this method, using sys.tables
如果你想避免 try-catch 解决方案，我建议使用 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;
}
回答by Mustafa Bükülmez
Very Simple
很简单
use YOUR_DATABASE --OPTIONAL
SELECT count(*) as Exist from INFORMATION_SCHEMA.TABLES where table_name = 'YOUR_TABLE_NAME'
If the answer is 1, There is a table. If the answer is 0, There is no table.
如果答案是 1，则有一张桌子。如果答案是 0，则没有表。
