SQL Server判断表、视图、存储、函数是否已存在后再进行删除的方法

1.表

本博搜索博文名：SQL Server数据库已存在表，如何判断删除表后再重建的方法，已包含表的说明

• 查询

SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'A_CMD') AND type in (N'U')  --A_CMD 为表名

SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'A_CMD' --表名
                                                    AND TABLE_SCHEMA = 'dbo' 
                                                    AND TABLE_CATALOG ='TESTDB') --数据库名
————————————————
原文链接：https://blog.csdn.net/lovemelovefish/article/details/123222390

• 实例

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'A_CMD') AND type in (N'U'))
BEGIN
   DROP TABLE A_CMD
END
CREATE TABLE A_CMD		-- 派令
(
A_NO				VARCHAR(5)				NOT NULL  ,-- 编号 (例如：1)
CREATED_BY			VARCHAR(30)						  ,-- 创建人员
CREATION_DATE		DATETIME			DEFAULT GETDATE()-- 创建时间
);
CREATE UNIQUE INDEX A_CMD_UNI_IDX ON A_CMD(A_NO);  --添加索引，查询时会提高速度


IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'A_CMD' --表名
                                                    AND TABLE_SCHEMA = 'dbo' 
                                                    AND TABLE_CATALOG ='TESTDB') --数据库名
  DROP TABLE A_CMD

CREATE TABLE A_CMD		-- 派令
(
A_NO				VARCHAR(5)				NOT NULL  ,-- 编号 (例如：1)
CREATED_BY			VARCHAR(30)						  ,-- 创建人员
CREATION_DATE		DATETIME			DEFAULT GETDATE()-- 创建时间
);
CREATE UNIQUE INDEX A_CMD_UNI_IDX ON A_CMD(A_NO);  --添加索引，查询时会提高速度
————————————————
原文链接：https://blog.csdn.net/lovemelovefish/article/details/123222390

2.视图

• 查询

SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'X_VW') AND TYPE IN (N'V')


SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'X_VW') AND  OBJECTPROPERTY(object_id, N'IsVIEW') = 1


SELECT * FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = 'X_VW' --视图名
                                                    AND TABLE_SCHEMA = 'dbo' 
                                                    AND TABLE_CATALOG ='ZSASRS'--数据库名

• 实例

DROP  PROCEDURE A_X_SP
----------方法1-----------------------
--是否存在X_VW的视图
IF EXISTS(SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'X_VW') AND TYPE IN (N'V'))
--删除X_VW的视图
DROP  VIEW X_VW
----------方法2-----------------------
IF EXISTS(SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'X_VW') AND  OBJECTPROPERTY(object_id, N'IsVIEW') = 1)
DROP  VIEW X_VW
----------方法3-----------------------
IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = 'X_VW' --视图名
                                                    AND TABLE_SCHEMA = 'dbo' 
                                                    AND TABLE_CATALOG ='ZSASRS'--数据库名
                                                    )
DROP  VIEW X_VW 

3.存储

• 查询

SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'存储过程名') AND TYPE IN (N'P')
SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'存储过程名') AND  OBJECTPROPERTY(object_id, N'IsProcedure') = 1

• 实例

----------方法1-----------------------
--是否存在A_X_SP的存储过程
IF EXISTS(SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'A_X_SP') AND TYPE IN (N'P'))
--删除A_X_SP存储过程
DROP  PROCEDURE A_X_SP
----------方法2-----------------------
IF EXISTS(SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'A_X_SP') AND  OBJECTPROPERTY(object_id, N'IsProcedure') = 1)
--删除A_X_SP存储过程
DROP  PROCEDURE A_X_SP

4.函数

• 查询
  比照上面，将类名改为FUNCTION即可

• 实例
  比照上面，将类名改为FUNCTION即可