How to list out all Foreign Keys with "WITH NOCHECK" in SQL Server

Question

Does anyone know a query for listing out all foreign keys in a database with WITH NOCHECK description applied to it? (removing them will boost performance and stability).

Answer 1

The following will return the name of the foreign keys in the current database that are disabled i.e. WITH NOCHECK

For SQL Server 2005/2008:

select * from sys.foreign_keys where is_disabled=1 

---

There was some discussion in the answer about the difference between disabled & not trusted. What's below explains the differnce Here's some code to clarify the difference between 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 means the constraint is disabled

isnottrusted means that SQL Server does not trust that the column has been checked against the foreign key table.

Thus it cannot be assumed that re-enabling the foreign key constraint will be optimized. To ensure the optimizer trusts the column, it's best to drop the foreign key constraint & re-create it with the WITH CHECK option (4.)

Answer 2

SELECT * FROM sys.foreign_keys AS f Where Is_Not_Trusted = 1