IF (OBJECT_ID('FK', 'F') IS NOT NULL) doesn't work but IF EXISTS() does? 如果(OBJECT_ID('FK','F')不为空)不起作用,但IF EXISTS()起作用吗?
IF (OBJECT_ID('FK', 'F') IS NOT NULL) doesn't work but IF EXISTS() does? 如果(OBJECT_ID('FK','F')不为空)不起作用,但IF EXISTS()起作用吗?
使用SQL Server 2014,这个查询似乎不起作用(即,不删除外键)
IF (OBJECT_ID('FK', 'F') IS NOT NULL) BEGIN ALTER TABLE my_table DROP CONSTRAINT [FK] END
但是这个可以
IF EXISTS( SELECT * FROM sys.foreign_keys WHERE name = 'FK') BEGIN ALTER TABLE my_table DROP CONSTRAINT [FK] END
我正在努力理解为什么。特别是因为第一个查询曾经是有效的。
运行此查询会返回带有外键的行(类型为F
)
select * from dbo.sysobjects o where o.type = 'F' and name = 'FK'
但是运行此查询不会打印出找到的外键语句
IF (OBJECT_ID('FK', 'F') IS NOT NULL) BEGIN PRINT 'Found foreign key' END
评论中建议的查询结果
select LEN(name) as 'Len', CAST(name as varbinary(MAX)) as AsBinary, name from dbo.sysobjects o where o.type = 'F' and name = 'my_fk_name'
给我返回了
108 | 0x46004B005F0049........006F006E0049006400 | my_fk_name
和
select id from dbo.sysobjects o where o.type = 'F' and name = 'my_fk_name' select OBJECT_NAME(TheObjectId)
给我返回了
my_fk_name