Enable Disable All Triggers in a Database

  • Comments posted to this topic are about the item Enable Disable All Triggers in a Database

    Vinay Kumar
    Keep Learning - Keep Growing !!!

  • Hi

    I have this command that is more simple

    EXECUTE sp_msforeachtable "ALTER TABLE ? disable trigger ALL"


  • 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];

  • Refer Here:

  • Thanks for the script.

  • This turned out to be very handy for our conversion. Thanks again.

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

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