Status of All Constraints in a Database.

One of my developers today reached out to me about knowing the status of the constraints and triggers in a database. He wanted to know whether a constraint was in enabled or disabled state.

The reason behind this question was that he was planning a huge data load in that database and was planning to disable all the constraints and triggers before the load and put the constraints back in the same state after the data load. He wanted to know if some constraints were disabled on purpose and did not want to enable them by mistake after the data load.

Here are some facts about the status change of constrains

• Default, Unique and Primary Key constraints cannot be disabled. 
• Check or Foreign Key constraints can be disabled and enabled.

So, If a default, Unique or Primary Key constraint exists on the database, it can only be in the active state.

The following two queries can give the list of Check and Foreign Key constraints with their status.

SELECT name AS check_constraint_name,

       OBJECT_NAME(parent_object_id) Parent_Object,

       CASE is_disabled WHEN 1 THEN 'DISABLED' ELSE 'ENABLED' END AS STATUS

FROM   sys.check_constraints

SELECT name AS ForeignKeyName,

       OBJECT_NAME(parent_object_id) AS TableName,

       CASE is_disabled WHEN 1 THEN 'DISABLED' ELSE 'ENABLED' END AS STATUS

FROM   sys.foreign_keys

I gave him one more query that would give the list of triggers in the database with their status.

SELECT name as trigger_name,

       OBJECT_NAME(parent_id) parent_object,

       CASE is_disabled WHEN 1 THEN 'DISABLED' ELSE 'ENABLED' END AS STATUS

FROM   sys.triggers

WHERE  OBJECT_NAME(parent_id) IS NOT NULL

 

Hope this helps.

Thanks and Regards,

Siva.