Check if table exists in SQL Server

技术背景

在SQL Server中，有时需要在执行某些操作之前检查特定的表是否存在。例如，在创建表之前检查该表是否已经存在，避免重复创建；或者在查询表数据之前确认表存在，防止因表不存在而导致的错误。

实现步骤

使用INFORMATION_SCHEMA.TABLES视图

INFORMATION_SCHEMA视图在很多不同的数据库中大多是标准的，并且版本之间很少变化。以下是检查指定模式和表名的表是否存在的示例：

IF (EXISTS (SELECT * 
             FROM INFORMATION_SCHEMA.TABLES 
             WHERE TABLE_SCHEMA = 'TheSchema' 
             AND  TABLE_NAME = 'TheTable'))
BEGIN
    -- Do Stuff
END

检查临时表

使用OBJECT_ID函数检查临时表是否存在：

if OBJECT_ID('tempdb..#test') is not null
    -- temp table exists

通用的OBJECT_ID方式

IF OBJECT_ID('*objectName*', 'U') IS NOT NULL
    -- 表存在

检查不同数据库中的表

if exists (select * from MyOtherDatabase.sys.tables where name = 'MyTable')
    print 'Exists'

SQL Server 2016及以上版本

可以使用drop table if exists简化操作：

drop table if exists mytablename

另一种检查方式

IF (OBJECT_ID('TableName') IS NOT NULL )
BEGIN
    PRINT 'Table Exists'
END
ELSE
BEGIN 
    PRINT 'Table NOT Exists'
END

在不同数据库中工作

DECLARE @Catalog VARCHAR(255)
SET @Catalog = 'MyDatabase'

DECLARE @Schema VARCHAR(255)
SET @Schema = 'dbo'

DECLARE @Table VARCHAR(255)
SET @Table = 'MyTable'

IF (EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES   
    WHERE TABLE_CATALOG = @Catalog 
      AND TABLE_SCHEMA = @Schema 
      AND TABLE_NAME = @Table))
BEGIN
    -- do stuff
END

创建存储过程检查

create procedure Table_Exists
@tbl varchar(50)
as
return (select count(*) from sysobjects where type = 'U' and name = @tbl)
go

SQL Server 2000中的检查

IF EXISTS(SELECT 1 FROM sysobjects WHERE type = 'U' and name = 'MYTABLENAME')
BEGIN
    SELECT 1 AS 'res' 
END

核心代码

以下是一个完整的示例，接收表名作为参数，检查表是否存在并返回相关信息：

PRINT 'THE SERVER IS ' + @@SERVERNAME
--select db_name()
PRINT 'THE DATABASE IS ' + db_NAME() 
PRINT ''
GO

SET NOCOUNT ON
GO

--===================================================================================
-- @TableName is the parameter
-- the object we want to deal with (it might be an indexed view or a table)
-- the schema might or might not be specified
-- when not specified it is DBO
--===================================================================================

DECLARE @TableName SYSNAME

SELECT @TableName = 'Facts.FactBackOrder'
--===================================================================================
--===================================================================================
DECLARE @Schema SYSNAME
DECLARE @I INT
DECLARE @Z INT 

SELECT @TableName = LTRIM(RTRIM(@TableName))
SELECT @Z = LEN(@TableName)

IF (@Z = 0) BEGIN
    RAISERROR('Invalid @Tablename passed.',16,1)
END 

SELECT @I = CHARINDEX('.',@TableName )
--SELECT @TableName ,@I

IF @I > 0 BEGIN
    --===================================================================================
    -- a schema and table name have been passed
    -- example Facts.FactBackOrder 
    -- @Schema = Fact
    -- @TableName = FactBackOrder
    --===================================================================================
    SELECT @Schema    = SUBSTRING(@TABLENAME,1,@I-1)
    SELECT @TableName = SUBSTRING(@TABLENAME,@I+1,@Z-@I)
END
ELSE BEGIN
    --===================================================================================
    -- just a table name have been passed
    -- so the schema will be dbo
    -- example Orders
    -- @Schema = dbo
    -- @TableName = Orders
    --===================================================================================
    SELECT @Schema    = 'DBO'     
END

--===================================================================================
-- Check whether the @SchemaName is valid in the current database
--===================================================================================
IF NOT EXISTS ( SELECT * FROM INFORMATION_SCHEMA.SCHEMATA K WHERE K.[SCHEMA_NAME] = @Schema ) BEGIN
    RAISERROR('Invalid Schema Name.',16,1)
END 

--SELECT @Schema  as [@Schema]
--      ,@TableName as [@TableName]

DECLARE @R1 TABLE (
    THE_NAME SYSNAME
   ,THE_SCHEMA SYSNAME
   ,THE_TABLE SYSNAME
   ,OBJECT_ID INT
   ,THE_TYPE SYSNAME
   ,PRIMARY KEY CLUSTERED (THE_SCHEMA,THE_NAME)
)

;WITH RADHE_01 AS (
    SELECT QUOTENAME(SCHEMA_NAME(O.schema_id)) + '.' + QUOTENAME(O.NAME) AS [the_name]
          ,the_schema=SCHEMA_NAME(O.schema_id)
          ,the_table=O.NAME
          ,object_id =o.object_id 
          ,[the_type]= CASE WHEN O.TYPE = 'U' THEN 'Table' ELSE 'View' END 
    from sys.objects O
    where O.is_ms_shipped = 0
    AND O.TYPE IN ('U','V')
)
INSERT INTO @R1 (
    THE_NAME 
   ,THE_SCHEMA 
   ,THE_TABLE 
   ,OBJECT_ID
   ,THE_TYPE 
)
SELECT  the_name
       ,the_schema
       ,the_table
       ,object_id
       ,the_type
FROM RADHE_01
WHERE the_schema = @Schema 
  AND the_table  = @TableName

IF (@@ROWCOUNT = 0) BEGIN 
    RAISERROR('Invalid Table Name.',16,1)
END 
ELSE BEGIN
    SELECT     THE_NAME 
              ,THE_SCHEMA 
              ,THE_TABLE 
              ,OBJECT_ID
              ,THE_TYPE 
    FROM @R1
END 

最佳实践

• 优先使用INFORMATION_SCHEMA视图，因为它在不同数据库中大多是标准的，版本兼容性好。
• 如果需要检查临时表，使用OBJECT_ID函数。
• 在SQL Server 2016及以上版本，使用drop table if exists简化表的删除操作。

常见问题

权限问题

在执行某些操作时可能会遇到权限不足的问题，需要确保当前用户具有足够的权限来访问相关的系统表和执行相应的操作。

版本差异

不同版本的SQL Server可能对某些函数和语法的支持有所不同，需要根据实际使用的版本选择合适的方法。

错误处理

在编写检查表是否存在的代码时，要考虑到各种可能的错误情况，例如传入空字符串、错误的模式名或表名等，并进行相应的错误处理。