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.
No comments:
Post a Comment