sql-server - 如何列出SQL Server中所有带有 "WITH NOCHECK"的外键

Question

有谁知道一个查询，用于列出数据库中所有外键并应用 WITH NOCHECK 描述？ (删除它们将提高性能和稳定性)。

Answer 1

以下内容将返回当前数据库中禁用的外键名称，即WITH NOCHECK

对于 SQL Server 2005/2008:

select * from sys.foreign_keys where is_disabled=1

<小时/>答案中有一些关于禁用和不信任之间的区别的讨论。下面解释了其中的区别下面是一些代码来阐明 is_disabled 和 isnotrusted 之间的区别。

-- drop table t1
-- drop table t2
create table t1(i int not null, fk int not null)
create table t2(i int not null)
-- create primary key on t2
alter table t2
add constraint pk_1 primary key (i)
-- create foriegn key on t1
alter table t1
add constraint fk_1 foreign key (fk)
    references t2 (i)
--insert some records
insert t2 values(100)
insert t2 values(200)
insert t2 values(300)
insert t2 values(400)
insert t2 values(500)
insert t1 values(1,100)
insert t1 values(2,100)
insert t1 values(3,500)
insert t1 values(4,500)
----------------------------
-- 1. enabled and trusted
select name,is_disabled,is_not_trusted from sys.foreign_keys
GO

-- 2. disable the constraint
alter table t1 NOCHECK CONSTRAINT fk_1
select name,is_disabled,is_not_trusted from sys.foreign_keys
GO

-- 3. re-enable constraint, data isnt checked, so not trusted.
-- this means the optimizer will still have to check the column
alter table  t1 CHECK CONSTRAINT fk_1 
select name,is_disabled,is_not_trusted from sys.foreign_keys
GO

--4. drop the foreign key constraint & re-add 
-- it making sure its checked
-- constraint is then enabled and trusted
alter table t1  DROP CONSTRAINT fk_1
alter table t1 WITH CHECK 
add constraint fk_1 foreign key (fk)
    references t2 (i)
select name,is_disabled,is_not_trusted from sys.foreign_keys
GO


--5. drop the foreign key constraint & add but dont check
-- constraint is then enabled, but not trusted
alter table t1  DROP CONSTRAINT fk_1
alter table t1 WITH NOCHECK 
add constraint fk_1 foreign key (fk)
    references t2 (i)
select name,is_disabled,is_not_trusted from sys.foreign_keys
GO

is_disabled 表示约束已禁用

isnottrusted 表示 SQL Server 不信任已根据外键表检查该列。

因此不能假设重新启用外键约束将会得到优化。为了确保优化器信任该列，最好删除外键约束并使用 WITH CHECK 选项重新创建它 (4.)