Sharpen your SQL Skills

DISCLAIMER: The product links on this page are affiliate links which means I get a commission if you purchase anything through them,
but it will not cost you anything extra. For more information see my Affiliate Disclaimer page.

For more great resources, browse the Pineapple Place Store.

List all Disabled Triggers

Tag: trigger, T-SQL, Inventory

If you have triggers running against your database tables then you probably want to make sure they are turned on or off at the right times. Here's a script to check the status of your triggers.

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 --Use 1 to list Disabled triggers and 0 for Enabled triggers

Share this Tidbit
Copyright © 2008-2013 SQL Tidbits™