Monday, March 3, 2014

List all Disabled Trigger

Use below query to get a list of all disabled trigger in a database:

SELECT db_name() AS [Database Name],
T.[Name] AS [TableName],
TR.[Name] AS [TriggerName],
[Status] = CASE WHEN OBJECTPROPERTY(TR.[ID], 'ExecIsTriggerDisabled') = 1
THEN 'Disabled' ELSE 'Enabled' END
FROM sysobjects T JOIN sysobjects TR ON T.[ID] = TR.[parent_obj]
WHERE T.[xtype] = 'U' AND TR.[xtype] = 'TR' AND OBJECTPROPERTY(TR.[ID], 'ExecIsTriggerDisabled') = 1 

Here you can use 0 to list Enabled triggers and 1 for Disabled triggers.

No comments:

Post a Comment