为了开发人员和其他dba的利益，这里只是添加了一些内容

接收@Tablename作为参数的脚本

(可能包含也可能不包含模式名)并返回下面的信息。表存在:

the_name                object_id   the_schema  the_table       the_type
[Facts].[FactBackOrder] 758293761   Facts       FactBackOrder   Table

我制作了这个脚本，以便在每次需要测试表或视图是否存在时在其他脚本中使用，当它存在时，获取它的object_id用于其他目的。

当您传递了空字符串、错误的模式名或错误的表名时，将引发错误。

例如，它可以在过程中返回-1。

例如，我有一个名为“事实”的表格。在我的数据仓库数据库之一的FactBackOrder。

我是这样做到的:

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 

我以创建一个视图为例。

因为ALTER/CREATE命令不能在BEGIN/END块中。在创建之前，您需要测试是否存在并删除它

IF Object_ID('TestView') IS NOT NULL
DROP VIEW TestView

GO

CREATE VIEW TestView
   as
   . . .

GO

如果您担心权限丢失，您也可以编写GRANT语句，并在最后重新运行它们。

你可以把create/alter包装成一个字符串，然后执行EXEC——对于大的视图来说，这可能会很难看

DECLARE @SQL as varchar(4000)

-- set to body of view
SET @SQL = 'SELECT X, Y, Z FROM TABLE' 

IF Object_ID('TestView') IS NULL
    SET @SQL = 'CREATE VIEW TestView AS ' + @SQL
ELSE    
    SET @SQL = 'ALTER VIEW TestView AS ' + @SQL

IF OBJECT_ID('mytablename') IS NOT NULL 

我知道这是一个老问题，但如果你计划经常打电话，我发现这种可能性。

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

IF EXISTS (
SELECT *
FROM INFORMATION_SCHEMA.TABLES
WHERE 
TABLE_CATALOG = 'Database Name' and
TABLE_NAME = 'Table Name' and 
TABLE_SCHEMA = 'Schema Name') -- Database and Schema name in where statement can be deleted

BEGIN
--TABLE EXISTS
END

ELSE BEGIN
--TABLE DOES NOT EXISTS
END

只是想提到一种情况，在这种情况下使用OBJECT_ID方法可能会更容易一些。INFORMATION_SCHEMA视图是每个数据库-下的对象

信息模式视图定义在一个名为 INFORMATION_SCHEMA。这个模式包含在每个数据库中。

https://msdn.microsoft.com/en-us/library/ms186778.aspx

因此，您访问的所有表都使用

IF EXISTS (SELECT 1 
           FROM [database].INFORMATION_SCHEMA.TABLES 
           WHERE TABLE_TYPE='BASE TABLE' 
           AND TABLE_NAME='mytablename') 
   SELECT 1 AS res ELSE SELECT 0 AS res;

将只反映什么是在[数据库]。如果您想检查另一个数据库中是否存在表，而不需要每次都动态更改[database]， OBJECT_ID将允许您开箱操作。除

IF OBJECT_ID (N'db1.schema.table1', N'U') IS NOT NULL 
   SELECT 1 AS res ELSE SELECT 0 AS res;

效果和

IF OBJECT_ID (N'db2.schema.table1', N'U') IS NOT NULL 
   SELECT 1 AS res ELSE SELECT 0 AS res;

SQL SERVER 2016编辑:

从2016年开始，微软通过添加if exists关键字来删除语句，简化了在删除之前检查不存在对象的功能。例如,

drop table if exists mytablename

将在一行代码中完成与OBJECT_ID / INFORMATION_SCHEMA包装器相同的事情。

https://blogs.msdn.microsoft.com/sqlserverstorageengine/2015/11/03/drop-if-exists-new-thing-in-sql-server-2016/