Sql-server – How to determine CHECK or NO CHECK for foreign Key using SQL Server System Tables

sql server

I need to know at a low level whether a foreign key is to be exported as CHECK or NO CHECK but can't find out where to find that information. I am using 

-- using INFORMATION_SCHEMA to enumerate foreign keys
-- for a particular referenced table
SELECT 
C.CONSTRAINT_NAME [constraint_name] 
,C.TABLE_NAME [referencing_table_name] 
,KCU.COLUMN_NAME [referencing_column_name] 
,C2.TABLE_NAME [referenced_table_name] 
,KCU2.COLUMN_NAME [referenced_column_name]
,RC.DELETE_RULE delete_referential_action_desc 
, RC.UPDATE_RULE update_referential_action_desc
FROM   INFORMATION_SCHEMA.TABLE_CONSTRAINTS C 
   INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU 
     ON C.CONSTRAINT_SCHEMA = KCU.CONSTRAINT_SCHEMA 
        AND C.CONSTRAINT_NAME = KCU.CONSTRAINT_NAME 
   INNER JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS RC 
     ON C.CONSTRAINT_SCHEMA = RC.CONSTRAINT_SCHEMA 
        AND C.CONSTRAINT_NAME = RC.CONSTRAINT_NAME 
   INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS C2 
     ON RC.UNIQUE_CONSTRAINT_SCHEMA = C2.CONSTRAINT_SCHEMA 
        AND RC.UNIQUE_CONSTRAINT_NAME = C2.CONSTRAINT_NAME 
   INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU2 
     ON C2.CONSTRAINT_SCHEMA = KCU2.CONSTRAINT_SCHEMA 
        AND C2.CONSTRAINT_NAME = KCU2.CONSTRAINT_NAME 
        AND KCU.ORDINAL_POSITION = KCU2.ORDINAL_POSITION 
WHERE  C.CONSTRAINT_TYPE = 'FOREIGN KEY' AND  C2.TABLE_NAME = 'SalesOrderHeader'

with the code coming from https://www.mssqltips.com/sqlservertip/4753/list-dependencies-for-sql-server-foreign-keys/

However I am unable to determine which field will tell me if a FK constraint should be CHECK or NO CHECK. How can I determine this, thanks.

Best Answer

You will need to query "sys.foreign_keys" system table.
Column "is_disabled" will provide you this info.
As far as I know this information is not available in INFORMATION SCHEMA.

You can use following query to get similar results:

SELECT 
FK.name [constraint_name] 
,T.name [referencing_table_name] 
,TabC.name [referencing_column_name] 
,RefT.name [referenced_table_name] 
,RefC.name [referenced_column_name]
,FK.delete_referential_action_desc delete_referential_action_desc 
,FK.update_referential_action_desc update_referential_action_desc
,FK.is_disabled
,FK.is_not_trusted
FROM   sys.foreign_keys AS FK
   INNER JOIN sys.foreign_key_columns FKC 
     ON FK.object_id = FKC.constraint_object_id
   INNER JOIN sys.tables AS T
     ON T.object_id = FK.parent_object_id
   INNER JOIN sys.columns AS TabC
     ON TabC.column_id = FKC.constraint_column_id
     AND TabC.object_id = FKC.parent_object_id
   INNER JOIN sys.tables AS RefT
     ON RefT.object_id = FK.referenced_object_id
   INNER JOIN sys.columns AS RefC
     ON RefC.column_id = FKC.referenced_column_id
     AND RefC.object_id = FKC.referenced_object_id
Related Question