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即可