Enable Disable All Triggers in a Database

Question

Post reply

Enable Disable All Triggers in a Database

Vinay Kumar

SSCertifiable

Points: 6099
More actions
January 30, 2008 at 3:20 pm

#202862

Comments posted to this topic are about the item Enable Disable All Triggers in a Database
Thanks
Vinay Kumar
-----------------------------------------------------------------
Keep Learning - Keep Growing !!!

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Adrian Aleksandrowicz-276567 Valued Member Points: 55 More actions · Answer 1

Hi

I have this command that is more simple

EXECUTE sp_msforeachtable "ALTER TABLE ? disable trigger ALL"

Adrian

Ralph D. Wilson II Old Hand Points: 394 More actions · Answer 2

I have updated the initial query so that it will indicate the enabled/disabled triggers in a SQL Server 2005 database.

SELECT T.[name] as TableName,
TR.[Name] as TriggerName,
CASE WHEN 1=OBJECTPROPERTY(TR.[object_id], 'ExecIsTriggerDisabled')
THEN 'Disabled'
ELSE 'Enabled'
END Status
FROM sys.objects T
INNER JOIN sys.triggers TR
ON T.[object_id] = TR.parent_id
WHERE (T.type = 'U' or T.type = 'V')
ORDER BY T.[name], TR.[name];