https://blog.csdn.net/qq_44034384/article/details/106090525
在对数据库进行新增操作时,往往需要进行判断是否存在之后方可进行相关操作,如下所示:
可使用——object_id(‘name’,‘type’)方法
--创建学生表数据备份表 判断是否存在,不存在则创建 if(OBJECT_ID('studentbak','U')is null) --is not null create table studentbak ( student_id int identity(1,1) primary key, student_name varchar(10), student_age int, student_sex varchar(2) );
if object_id('对象名') is not null 执行语句 --尽量写上对象类型,避免因为同名现象,而查询出错 --或者 if object_id('对象名','对象类型') is not null 执行语句 --或者 if object_id(N'对象名',N'对象类型') is not null 执行语句 --等同于:select ID from sysobjects where name='对象名' and type='对象类型'
注意:object_id:返回数据库对象标识号。N是显式的将非unicode字符转成unicode字符,它来自 SQL-92 标准中的。在这里,我们不写也行
可选对象类型如下:
AF = 聚合函数 (CLR)
C = CHECK 约束
D = DEFAULT(约束或独立)
F = FOREIGN KEY 约束
FN = SQL 标量函数
FS = 程序集 (CLR) 标量函数
FT = 程序集 (CLR) 表值函数
IF = SQL 内联表值函数
IT = 内部表
P = SQL 存储过程
PC = 程序集 (CLR) 存储过程
PG = 计划指南
PK = PRIMARY KEY 约束
R = 规则(旧式,独立)
RF = 复制筛选过程
S = 系统基表
SN = 同义词
SQ = 服务队列
TA = 程序集 (CLR) DML 触发器
TF = SQL 表值函数
TR = SQL DML 触发器
U = 表(用户定义类型)
UQ = UNIQUE 约束
V = 视图
X = 扩展存储过程
以下为3种不同写法: 删除用户表 if object_id(N'表名',N'U') is not null drop table 表名 1 删除SQL表值函数 if object_id('函数名','fn') is not null drop function 函数名 1 删除存储过程 if object_id('存储过程名') is not null drop procedure 存储过程名 --尽量写上对象类型,避免因为同名现象,而查询出错 --if object_id('存储过程名','P') is not null drop procedure 存储过程名
注意:在数据库中有一个系统表sysobjects,里面存储了数据库各个对象的信息。可以查询下看看结果。可以看出每个对象都有一个ID,这个表存储了表,存储过程,触发器,视图等相关信息
object_id就是根据对象名称返回该对象的id
object_name是根据对象id返回对象名称
select object_id(对象名)等同于: select id from sysobjects where name=对象名
select object_name(id号)等同于: select name from sysobjects where id=id号
--会取出表为student的ID值 select object_id('student','u') as ID --等价于 select ID from sysobjects where name='student' and type='u' select object_id('student') --等价于 select id from sysobjects where name='student' select object_name(245575913) --等价于 select name from sysobjects where id=245575913 --注:245575913 为本地查询出来的结果,不同的环境,可能范围值不同
应用测试:
--------------------表U已经存在-------------------- if(OBJECT_ID('QS_QUEUE','U')is not null) --is not null print 'QS_QUEUE 表已经存在' if(OBJECT_ID('QS_PARAM','U')is not null) --is not null print 'QS_PARAM 表已经存在' --------------------试图V已经存在-------------------- if(OBJECT_ID('VIEW_PRINT','V')is not null) --is not null print 'VIEW_PRINT 试图已经存在' --------------------触发器TR已经存在-------------------- if(OBJECT_ID('trigger_student_Insert','TR')is not null) --is not null print 'trigger_student_Insert 触发器已经存在'
--新增表 IF NOT EXISTS ( SELECT * FROM sysobjects WHERE id = object_id('DICT_CONFLICTRULECONFIG') AND OBJECTPROPERTY(id, 'IsUserTable') = 1) CREATE TABLE [dbo].DICT_CONFLICTRULECONFIG( [ID] [int] IDENTITY(1,1) NOT NULL CONSTRAINT [PK_DICT_CONFLICTRULECONFIG] PRIMARY KEY CLUSTERED ( [ID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO --新增或修改索引 IF EXISTS (SELECT name FROM sys.indexes WHERE name = N'DF_QUEUEDETAIL_InsertDateTime') DROP INDEX [DF_QUEUEDETAIL_InsertDateTime] ON [dbo].[QUEUEDETAIL] WITH ( ONLINE = OFF ) GO /****** Object: Index [DF_QUEUEDETAIL_InsertDateTime] Script Date: 2023-05-31 10:34:41 ******/ CREATE CLUSTERED INDEX [DF_QUEUEDETAIL_InsertDateTime] ON [dbo].[QUEUEDETAIL] ( --InsertDateTime desc, APPOINTSID DESC, [APPOINTDATE] DESC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] GO --表中新增数据 if not exists (select * from [DICT_PARAM] where PARAMKEY='OnLineCodeNum') INSERT INTO [dbo].[DICT_PARAM] ([ID],[PARAMTYPE],[PARAMNAME],[PARAMVALUE],[PARAMKEY],[PARAMUINAME] ,[PARAMREMARK],[USERID],[MACADRESS],[IPADRESS],[ISVISIBLE], [ORGANIZATIONCODE],[ORGANIZATION],[PARAMSETTYPE],[PARAMITEMS]) VALUES (NEWID() --[ID], ,'系统参数' --[PARAMTYPE] ,'线上号源数量' --[PARAMNAME] ,'门诊线上号源数量:' --[PARAMVALUE] ,'OnLineCodeNum' --[PARAMKEY] ,'线上号源数量' --[PARAMUINAME] ,'线上号源数量' -- [PARAMREMARK] ,'' -- [USERID] ,'' --[MACADRESS] ,'' --[IPADRESS] ,'1' --[ISVISIBLE] ,'' --[ORGANIZATIONCODE] ,'' --[ORGANIZATION] ,'TextBox' --[PARAMSETTYPE] ,'') -- [PARAMITEMS] go --表中新增字段 if not exists(select * from syscolumns where id=object_id('DICT_DEPARTMENT') and name='Orgnization') begin ALTER TABLE DICT_DEPARTMENT ADD Orgnization VARCHAR(100) NULL; end --新增试图 IF EXISTS(SELECT * FROM sysobjects WHERE id = object_id(N'VIEW_CHECKITEM_CONFLICTRULE') AND OBJECTPROPERTY(id, N'IsView') = 1) DROP VIEW VIEW_CHECKITEM_CONFLICTRULE GO CREATE VIEW VIEW_CHECKITEM_CONFLICTRULE AS SELECT b.ID as ID, CONVERT(varchar,a.ID) as CHECKITEMID, ISNULL(b.SORTNO,0) as SORTNO, ISNULL(b.DELETED,0) as DELETED from CHECKITEMWORKS a LEFT JOIN DICT_CONFLICTRULE b on a.ID=b.CHECKITEMID GO