正文
SqlServer——判断对象是否存在
提示：扫一扫查出行【扫一扫了解最新限行尾号】
复制提示
对以下对象判断是否存在：database、table、proc、触发器、临时表、索引。对于这些对象的判断是通过数据表 SysObjects来获得的。
一、基础知识
1、SysObjects系统表
对于这些对象的判断是通过数据表 SysObjects来获得的。运行 sp_help sysobjects ，如下图：
特别要注意 1、2、3、14。其中 name 表示对象的名称，id 表示SQL数据库分配给对象的 id 号，字段 xtype 和 type 均表示该对象的类型，类型如下：
| xtype | type | 表达的意思 |
| C | CHECK 约束 | |
| D | 默认值或 DEFAULT 约束 | |
| F | FOREIGN KEY 约束 | |
| L | 日志 | |
| FN | 标量函数 | |
| IF | 内嵌表函数 | |
| P | 存储过程 | |
| PK | K | PRIMARY KEY 约束 |
| RF | 复制筛选存储过程 | |
| S | 系统表 | |
| TF | 表函数 | |
| TR | 触发器 | |
| U | 用户表 | |
| UQ | K | UNIQUE 约束 |
| V | 视图 | |
| X | 扩展存储过程 |
2、sysdatabases系统表
SQL Server 上的每个数据库在表中占一行。该表存储在 master 数据库中。其中，重要的字段有下面两个。name （表示库的名字） dbid（表示库的ID）
dbid从1到4是系统的。包括 master、tempdb、model、msdb 这四个库。
可以利用SQL语句：select * from master.dbo.sysdatabases 查询出所有的库名.
3、syscolumns系统表
每个表和视图中的每列在表中占一行，存储过程中的每个参数在表中也占一行。该表位于每一个数据库中。 包含所在数据库中的所有表的所有字段。
部分字段如下：
重要的字段包含以下几个。name（字段名称 ）、 id（表ID号）、 colid（字段ID号）
4、object_id（）和 Object_name（）函数
数据库中每个对像都有一个唯一的ID值，用Object_name(id)可以根据ID值得到对像的名称，object_id(name)可以根据对像名称得到对象的ID,但是 object_id() 只能返回用户创建的对像的ID,像以sys开头的表都是系统表所以返回不了。
(1)select object_id(对象名) <==> select id from sysobjects where name=对象名。注: 当指定临时表名时，除非当前数据库为 tempdb，否则必须在该临时表名之前加上数据库名称。 例如：SELECT OBJECT_ID('tempdb..#mytemptable')。
(2)select object_name(id号) <==> select name from sysobjects where id=id号
object_id () :返回架构范围内对象的数据库对象标识号。
语法：
OBJECT_ID ( '[ database_name . [ schema_name ] . | schema_name . ] object_name' [ ,'object_type' ] )或简化为：OBJECT_ID ( 'object_name' [ ,'object_type' ] )
参数：
- object_name ：要使用的对象。object_name 的数据类型为 varchar 或 nvarchar。如果 object_name 的数据类型为 varchar，则它将隐式转换为 nvarchar。可以选择是否指定数据库和架构名称。
- object_type ：架构范围的对象类型。object_type 的数据类型为 varchar 或 nvarchar。如果 object_type 的数据类型为 varchar，则它将隐式转换为 nvarchar。有关对象类型的列表，请参阅上面表格中的 type 列。
- 返回类型 ：int；对于空间索引，OBJECT_ID 返回 NULL；出现错误时，返回 NULL。用户只能查看 为该用户所有的安全对象，或已授予该用户对该安全对象的权限。 也就是说，如果用户对该对象没有任何权限，则那些会生成元数据的内置函数（如 OBJECT_ID）可能返回 NULL
返回当前数据库中架构范围内的对象的有关信息。 有关架构范围内对象的列表，请参阅 sys.objects (Transact-SQL)。 不能将此函数用于不属于架构范围内的对象，如数据定义语言 (DDL) 触发器和事件通知。
语法：OBJECTPROPERTY ( id , property )
参数：
id：是表示当前数据库中架构范围内的对象 ID 的表达式。 id 的数据类型为 int。
property：一个表达式，表示要为 id 指定的对象返回的信息。 property 可以是下列值之一。
| property | 说明 | 返回值 |
| IsCheckCnst | CHECK 约束。 | 1 = True；0 = False |
| IsDefault | 绑定的默认值。 | 1 = True；0 = False |
| IsDefaultCnst | DEFAULT 约束。 | 1 = True；0 = False |
| IsExecuted | 可执行对象（视图、过程、函数或触发器）。 | 1 = True；0 = False |
| IsEncrypted | 是否已加密 | 1 = 已加密；0 = 未加密 |
| IsPrimaryKey | PRIMARY KEY 约束。 | 1 = True；0 = False；NULL = 非函数，或对象 ID 无效。 |
| IsForeignKey | FOREIGN KEY 约束。 | 1 = True；0 = False |
| IsIndexed | 可以创建索引的表或视图。 | 1 = True；0 = False |
| IsProcedure | 存储过程 | 1 = True；0 = False |
| IsScalarFunction | 标量值函数。 | 1 = 标量值函数；0 = 非标量值函数 |
| IsTableFunction | 表值函数。 | 1 = 表值函数；0 = 非表值函数 |
| IsSchemaBound | 使用 SCHEMABINDING 创建的绑定到架构的函数或视图。 | 1 = 绑定到架构；0 = 不绑定到架构。 |
| IsSystemTable | 系统表。 | 1 = True；0 = False |
| IsTable | 表。 | 1 = True；0 = False |
| IsTrigger | 触发器。 | 1 = True；0 = False |
| IsUniqueCnst | UNIQUE 约束。 | 1 = True；0 = False |
| IsUserTable | 用户定义的表。 | 1 = True；0 = False |
| IsView | 视图。 | 1 = True；0 = False |
6、判断对象是否存在的方法：
方法一：从 sysobjects 表中获取指定 名称 或者 id 的对象，并且通过OBJECTPROPERTY( id , property)函数证明其为指定的对象类型；
方法二：利用 OBJECT_ID ( 'object_name' ,'object_type') 方法。当其 is not null 时即为指定类型对象；
方法三：从 sysobjects 表中获取指定 名称 或者 id 的对象，并且其 type 为指定值。
二、示例
1、判断数据库：
if exists (select * from sys.databases where name = ’数据库名’) --从sys.databases表中获取
PRINT '存在'
ELSE
PRINT'不存在'
2、判断表：
方法一：
if exists (select * from dbo.SysObjects where id = object_id(N'[表名]') and OBJECTPROPERTY(ID, 'IsTable') = 1)
PRINT '存在'
ELSE
PRINT'不存在'
方法二：
if object_id(N'tablename',N'U') is not null
print '存在'
else
print '不存在'
方法三：
if exists (select * from sysobjects where id = object_id(N'[表名]') and type in('S','U')) --字段 type 值应为'S' 或 'U'
print '存在'
else
print '不存在'
3、判断存储过程
方法一：
if exists (select * from sysobjects where id = object_id(N'[存储过程名]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
print '存在'
else
print '不存在'
方法二：
if object_id(N'tablename',N'P') is not null
print '存在'
else
print '不存在'
方法三：
if exists (select * from sysobjects where id = object_id(N'[存储过程名]') and type in('S','P'))
print '存在'
else
print '不存在'
4、判断临时表
方法一：
if exists(select * from tempdb.dbo.sysobjects where id=object_id('tempdb.dbo.##TEMP_TBL'))
PRINT '存在'
ELSE
PRINT'不存在'
方法二：
if exists (select * from tempdb.dbo.sysobjects where id = object_id(N'tempdb.dbo.#TEMP_TBL') and type='U')
PRINT '存在'
ELSE
PRINT'不存在'
注：在判断临时表时，必须要在系统数据库 tempdb 中判断。
5、判断视图
--SQL Server 2000
IF EXISTS (SELECT * FROM sysviews WHERE object_id = ’[dbo].[视图名]’
--SQL Server 2005
IF EXISTS (SELECT * FROM sys.views WHERE object_id = ’[dbo].[视图名]’
6、判断函数
if exists (select * from dbo.sysobjects where id = object_id(N’[dbo].[函数名]’) and xtype in(N’FN’, N’IF’, N’TF’))
drop function [dbo].[函数名]
7、判断列
方法一：
if exists(select * from syscolumns where id=object_id(’表名’) and name=’列名’)
alter table 表名 drop column 列名
方法二：
IF EXISTS (SELECT O.NAME AS 表名,C.NAME AS 列名 FROM SYSOBJECTS O INNER JOIN SYSCOLUMNS C ON O.ID=C.ID WHERE O.NAME= '表名' AND C.NAME = '列名')
PRINT '存在'
ELSE
PRINT'不存在'
8、判断列是否自增列
if columnproperty(object_id(’table’),’col’,’IsIdentity’)=1
print ’自增列’
else
print ’不是自增列’
获取指定表中的自增列
SELECT * FROM sys.columns WHERE object_id=OBJECT_ID(’表名’) AND is_identity=1
9、 判断表中是否存在索引
if exists(select * from sysindexes where id=object_id(’表名’) and name=’索引名’)
print ’存在’
else
print ’不存在
10 、查看数据库中对象
SELECT * FROM sys.sysobjects WHERE name=’对象名’
