SQL SERVER判断表、视图、 存储过程、函数是否已建立再删除的方法

1.表

本博标题【SQL Server数据库已存在表，如何判断删除表后再重建的方法】已撰写
- 方法1
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N’表名’) AND type in (N’U’))
DROP TABLE 表名

--是否存在A_CMD的表
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

- 方法2

--是否存在A_CMD的表
IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'A_CMD' --表名
                                                    AND TABLE_SCHEMA = 'dbo' 
                                                    AND TABLE_CATALOG ='TESTDB') --数据库名
 --存在则删除
 DROP TABLE A_CMD
————————————————
版权声明：本文为CSDN博主「迪丽热爱」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。
原文链接：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’–数据库名

----------方法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--------------------
--是否存在A_X_SP的存储过程
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 即可！