How to get list of All Disabled/Enabled Triggers in SQL Server Database

The below query can be used to get the list of all enabled/disabled Triggers in SQL Server Database.
You can use the is_disabled column in sys.triggers table to filter the records according to your requirement.


SELECT TBL.name                   AS TableName,
       
Schema_name(TBL.schema_id) AS Table_SchemaName,
       
TRG.name                   AS TriggerName,
       
TRG.parent_class_desc,
       
CASE
         
WHEN TRG.is_disabled = 0 THEN 'Enable'
         
ELSE 'Disable'
       
END                        AS TRG_Status 
FROM   sys.triggers TRG
       
INNER JOIN sys.tables TBL
               
ON TBL.OBJECT_ID = TRG.parent_id 
              
AND trg.is_disabled=1 --use this filter to get Disabled/Enabled Triggers

No comments:

Post a Comment