Search

Monday, February 3, 2014

List all Disabled Constraint in a database

Use below query to list out the disabled constraint in a database:

SELECT OBJECT_NAME(CONSTID) AS Constraint_Name,
OBJECT_NAME(FKEYID) AS Table_Name,
COL_NAME(FKEYID, FKEY) AS Column_Name,
OBJECT_NAME(RKEYID) AS Referenced_Table_Name,
COL_NAME(RKEYID, RKEY) AS Referenced_Column_Name, 
(CASE WHEN OBJECTPROPERTY(CONSTID, 'CNSTISDISABLED') = 0 THEN 'ENABLED' ELSE 'DISABLED' END) AS [Status]
FROM SYSFOREIGNKEYS
WHERE (CASE WHEN OBJECTPROPERTY(CONSTID, 'CNSTISDISABLED') = 0 THEN 'ENABLED' ELSE 'DISABLED' END) = 'DISABLED'
ORDER BY Table_Name, Constraint_Name, Referenced_Table_Name

No comments:

Post a Comment