为什么在检查表是否存在时使用OBJECT_ID [英] Why OBJECT_ID used while checking if a table exists or not
本文介绍了为什么在检查表是否存在时使用OBJECT_ID的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我需要检查SQL中的表是否存在。
I need to check if a table in SQL exist or not.
如果不存在,则必须自动创建一个表。
If not it must create one automatically.
现在我研究并找到了以下代码:
Now I researched and found this code:
IF NOT EXISTS (SELECT * FROM sys.objects
WHERE object_id = OBJECT_ID(N'[dbo].[YourTable]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[YourTable](
....
....
....
)
END
任何人都可以解释为什么它说 object_id = OBJECT_ID
的位置以及我应该放在什么位置吗? p>
Can anyone explain why it says where object_id = OBJECT_ID
and what should I put in its place?
推荐答案
我喜欢这样的语法:
if(object_id(N'[dbo].[YourTable]', 'U') is not null)
...
其中object_id将对象的2 char类型作为第二个参数。您可以在 sys.objects文档中找到以下列出的对象类型列表:
Where object_id takes the 2 char type of object as the second parameter. You can find the list of Object types listed below in the sys.objects documentation:
- AF =聚合函数(CLR)
- C = CHECK约束
- D =默认(约束或独立)
- F =外键约束
- FN = SQL标量函数
- FS =程序集(CLR)标量函数
- FT =程序集(CLR)表值函数
- IF = SQL内联表值函数
- IT =内部表
- P = SQL存储过程
- PC =程序集( CLR)存储过程
- PG =计划指南
- PK =主键约束
- R =规则(旧式,独立)
- RF =复制过滤器过程
- S =系统基本表
- SN =同义词
- SO =序列对象
- SQ =服务队列
- TA =组装件(CLR)D ML触发器
- TF = SQL表值函数
- TR = SQL DML触发器
- TT =表类型
- U =表(用户定义)
- UQ = UNIQUE约束
- V =视图
- X =扩展存储过程
- AF = Aggregate function (CLR)
- C = CHECK constraint
- D = DEFAULT (constraint or stand-alone)
- F = FOREIGN KEY constraint
- FN = SQL scalar function
- FS = Assembly (CLR) scalar-function
- FT = Assembly (CLR) table-valued function
- IF = SQL inline table-valued function
- IT = Internal table
- P = SQL Stored Procedure
- PC = Assembly (CLR) stored-procedure
- PG = Plan guide
- PK = PRIMARY KEY constraint
- R = Rule (old-style, stand-alone)
- RF = Replication-filter-procedure
- S = System base table
- SN = Synonym
- SO = Sequence object
- SQ = Service queue
- TA = Assembly (CLR) DML trigger
- TF = SQL table-valued-function
- TR = SQL DML trigger
- TT = Table type
- U = Table (user-defined)
- UQ = UNIQUE constraint
- V = View
- X = Extended stored procedure
这篇关于为什么在检查表是否存在时使用OBJECT_ID的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文