检查表是否存在，如果不存在，则在 SQL Server 2008 中创建它
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5952006/
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 table exists and if it doesn't exist, create it in SQL Server 2008
提问by Prady
I am writing a Stored procedure in SQL Server 2008. I need to check if a table exists in the database. If it doesn't then I need to create it.
我正在 SQL Server 2008 中编写存储过程。我需要检查数据库中是否存在表。如果没有，那么我需要创建它。
How do I do this?
我该怎么做呢？
回答by Philip Kelley
Just for contrast, I like using the object_id function as shown below. It's a bit easier to read, and you don't have to worry about sys.objects vs. sysobjects vs. sys.all_objects vs. sys.tables. Basic form:
只是为了对比，我喜欢使用 object_id 函数，如下所示。它更容易阅读，而且您不必担心 sys.objects 与 sysobjects 与 sys.all_objects 与 sys.tables。基本形式：
IF object_id('MyTable') is not null
PRINT 'Present!'
ELSE
PRINT 'Not accounted for'
Of course this will show as "Present" if there is anyobject present with that name. If you want to check just tables, you'd need:
当然，如果存在任何具有该名称的对象，这将显示为“存在” 。如果您只想检查表格，则需要：
IF object_id('MyTable', 'U') is not null
PRINT 'Present!'
ELSE
PRINT 'Not accounted for'
It works for temp tables as well:
它也适用于临时表：
IF object_id('tempdb.dbo.#MyTable') is not null
PRINT 'Present!'
ELSE
PRINT 'Not accounted for'
回答by SQLMenace
Something like this
像这样的东西
IF NOT EXISTS (SELECT * FROM sys.objects
WHERE object_id = OBJECT_ID(N'[dbo].[YourTable]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[YourTable](
....
....
....
)
END
回答by Vahid Farahmandian
Let us create a sample database with a table by the below script:
让我们通过以下脚本创建一个带有表的示例数据库：
CREATE DATABASE Test
GO
USE Test
GO
CREATE TABLE dbo.tblTest (Id INT, Name NVARCHAR(50))
Approach 1: Using INFORMATION_SCHEMA.TABLES view
方法 1：使用 INFORMATION_SCHEMA.TABLES 视图
We can write a query like below to check if a tblTest Table exists in the current database.
我们可以编写如下查询来检查当前数据库中是否存在 tblTest 表。
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = N'tblTest')
BEGIN
PRINT 'Table Exists'
END
The above query checks the existence of the tblTest table across all the schemas in the current database. Instead of this if you want to check the existence of the Table in a specified Schema and the Specified Database then we can write the above query as below:
上面的查询在当前数据库中的所有模式中检查 tblTest 表的存在。如果你想检查指定模式和指定数据库中表的存在，那么我们可以编写上述查询如下：
IF EXISTS (SELECT * FROM Test.INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = N'dbo' AND TABLE_NAME = N'tblTest')
BEGIN
PRINT 'Table Exists'
END
Pros of this Approach: INFORMATION_SCHEMA views are portable across different RDBMS systems, so porting to different RDBMS doesn't require any change.
这种方法的优点： INFORMATION_SCHEMA 视图可以跨不同的 RDBMS 系统移植，因此移植到不同的 RDBMS 不需要任何更改。
Approach 2: Using OBJECT_ID() function
方法二：使用 OBJECT_ID() 函数
We can use OBJECT_ID()function like below to check if a tblTest Table exists in the current database.
我们可以使用OBJECT_ID()如下函数来检查当前数据库中是否存在 tblTest 表。
IF OBJECT_ID(N'dbo.tblTest', N'U') IS NOT NULL
BEGIN
PRINT 'Table Exists'
END
Specifying the Database Name and Schema Name parts for the Table Name is optional. But specifying Database Name and Schema Name provides an option to check the existence of the table in the specified database and within a specified schema, instead of checking in the current database across all the schemas. The below query shows that even though the current database is MASTER database, we can check the existence of the tblTesttable in the dboschema in the Testdatabase.
为表名称指定数据库名称和架构名称部分是可选的。但是指定数据库名称和架构名称提供了一个选项来检查指定数据库和指定架构中的表是否存在，而不是跨所有架构检查当前数据库。下面的查询表明，即使当前数据库是 MASTER 数据库，我们也可以检查数据库tblTest中dboschema中表的存在Test。
USE MASTER
GO
IF OBJECT_ID(N'Test.dbo.tblTest', N'U') IS NOT NULL
BEGIN
PRINT 'Table Exists'
END
Pros: Easy to remember. One other notable point to mention about OBJECT_ID()function is: it provides an option to check the existence of the Temporary Table which is created in the current connection context. All other Approaches checks the existence of the Temporary Table created across all the connections context instead of just the current connection context. Below query shows how to check the existence of a Temporary Table using OBJECT_ID()function:
优点：容易记住。关于OBJECT_ID()函数的另一个值得注意的点是：它提供了一个选项来检查在当前连接上下文中创建的临时表是否存在。所有其他方法都会检查跨所有连接上下文创建的临时表的存在，而不仅仅是当前连接上下文。下面的查询显示了如何使用OBJECT_ID()函数检查临时表的存在：
CREATE TABLE #TempTable(ID INT)
GO
IF OBJECT_ID(N'TempDB.dbo.#TempTable', N'U') IS NOT NULL
BEGIN
PRINT 'Table Exists'
END
GO
Approach 3: Using sys.Objects Catalog View
方法 3：使用 sys.Objects 目录视图
We can use the Sys.Objectscatalog view to check the existence of the Table as shown below:
我们可以使用Sys.Objects目录视图来检查表是否存在，如下所示：
IF EXISTS(SELECT 1 FROM sys.Objects WHERE Object_id = OBJECT_ID(N'dbo.tblTest') AND Type = N'U')
BEGIN
PRINT 'Table Exists'
END
Approach 4: Using sys.Tables Catalog View
方法 4：使用 sys.Tables 目录视图
We can use the Sys.Tablescatalog view to check the existence of the Table as shown below:
我们可以使用Sys.Tables目录视图来检查表是否存在，如下所示：
IF EXISTS(SELECT 1 FROM sys.Tables WHERE Name = N'tblTest' AND Type = N'U')
BEGIN
PRINT 'Table Exists'
END
Sys.Tablescatalog view inherits the rows from the Sys.Objectscatalog view, Sys.objectscatalog view is referred to as base view where as sys.Tablesis referred to as derived view. Sys.Tableswill return the rows only for the Table objects whereas Sys.Objectview apart from returning the rows for table objects, it returns rows for the objects like: stored procedure, views etc.
Sys.Tables目录视图从目录视图继承行Sys.Objects，Sys.objects目录视图被称为基础视图，而sys.Tables被称为派生视图。Sys.Tables将仅返回表对象Sys.Object的行，而视图除了返回表对象的行之外，它还返回对象的行，例如：存储过程、视图等。
Approach 5: Avoid Using sys.sysobjects System table
方法五：避免使用 sys.sysobjects 系统表
We should avoid using sys.sysobjectsSystem Table directly, direct access to it will be deprecated in some future versions of the Sql Server. As per [Microsoft BOL][1] link, Microsoft is suggesting to use the catalog views sys.objects/sys.tablesinstead of sys.sysobjectssystem table directly.
我们应该避免sys.sysobjects直接使用System Table，直接访问它会在 Sql Server 的一些未来版本中被弃用。根据 [Microsoft BOL][1] 链接，Microsoft 建议直接使用目录视图sys.objects/sys.tables而不是sys.sysobjects系统表。
IF EXISTS(SELECT name FROM sys.sysobjects WHERE Name = N'tblTest' AND xtype = N'U')
BEGIN
PRINT 'Table Exists'
END
Reference: http://sqlhints.com/2014/04/13/how-to-check-if-a-table-exists-in-sql-server/
参考：http: //sqlhints.com/2014/04/13/how-to-check-if-a-table-exists-in-sql-server/
回答by veljasije
EDITED
已编辑
You can look into sys.tablesfor checking existence desired table:
您可以查看sys.tables以检查是否存在所需的表：
IF NOT EXISTS (SELECT * FROM sys.tables
WHERE name = N'YourTable' AND type = 'U')
BEGIN
CREATE TABLE [SchemaName].[YourTable](
....
....
....
)
END
回答by Vinod kumar
IF (EXISTS (SELECT *
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = 'd020915'))
BEGIN
declare @result int
set @result=1
select @result as result
END
回答by Aamir Shaikh
Declare @Username varchar(20)
Set @Username = 'Mike'
if not exists
(Select * from INFORMATION_SCHEMA.TABLES where TABLE_NAME = 'tblEmp')
Begin
Create table tblEmp (ID int primary key, Name varchar(50))
Print (@Username + ' Table created successfully')
End
Else
Begin
Print (@Username + ' : this Table Already exists in the database')
End
回答by Only You
Try the following statement to check for existence of a table in the database:
尝试以下语句来检查数据库中是否存在表：
If not exists (select name from sysobjects where name = 'tablename')
You may create the table inside the if block.
您可以在 if 块内创建表。
回答by RaM
If I am not wrong, this should work:
如果我没有错，这应该有效：
if not exists (Select 1 from tableName)
create table ...
