检测表和存储过程等对象是否存在的方法总结

博客前言：

在关于数据库项目中一般都会创建一些table, view, schema, synonym, trigger, function, procedure这些对象。在发布到production的时候通常都会检验这些对象是否已经被创建。下面就来总结一些检测方法。

环境： SQL SERVER 2008

具体方法：

Schema:

IF NOT EXISTS (SELECT * FROM sys.schemas WHERE NAME = 'schema_nm')
CREATE SCHEMA schema_nm
GO

注意事项：假如该schema已经被创建过，并且已经有对象属于该schema，再删除它就会报错。因此最好不要写drop schema的语句。

Table:

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'table_nm') AND type in (N'U'))
DROP TABLE table_nm
GO

View:

IF EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'view_nm'))
DROP VIEW view_nm
GO

Synonym:

IF EXISTS (SELECT * FROM sys.synonyms s JOIN sys.schemas sch ON s.schema_id = sch.schema_id
WHERE s.name = N'synonym_nm' AND sch.name = 'schema_nm')
DROP SYNONYM schema_nm.synonym_nm
GO

Function:

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'function_nm') AND TYPE IN (N'FN', N'IF', N'TF', N'FS', N'FT'))

GO

Procedure:

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'procedure_nm') AND type in (N'P', N'PC'))
DROP PROCEDURE procedure_nm
GO

总结：

监测到对象已经存在后不一定要drop掉，也可以写一些alter这些对象的语句操作，比如为表增加column之类的。