SQL Script to Enable/Disable all triggers in a Database

One thing I find myself doing frequently is enabling/disabling triggers in an InforCRM database, especially with users who are not utilizing any of the newer ERP integration features found in current versions of InforCRM.

This script can be used to mass enable or disable all triggers in a database. Change which Declaration of the @action variable to switch between Enabling and Disabling triggers. Running this script will generate the statements to change the state of the triggers, while also running them. You can disable the Exec line to just get a list of Enable/Disable statements that you can run yourself.

/* Enable/Disable triggers script       */
/* Jason Buss - 2018-04-19              */
/* Enable line for action you want to perform (enable/disable) and run script in it's entirety */

DECLARE @action VARCHAR(8) = 'DISABLE';
--DECLARE @action varchar(8) = 'ENABLE';

DECLARE @catalog VARCHAR(10) = 'sysdba';  --or dbo
DECLARE @disabled BIT= 0;
DECLARE @stmt VARCHAR(255);
DECLARE @loop INT= 0;
DECLARE @cnt INT;
DECLARE @totalcnt INT;

IF(@action = 'ENABLE')
    SET @disabled = 1;

SELECT @cnt = COUNT(*)
FROM sys.triggers
WHERE is_disabled = @disabled;
SELECT @totalcnt = COUNT(*)
FROM sys.triggers;

IF @cnt <> 0
    BEGIN
        WHILE @loop <> @cnt
            BEGIN
                SELECT TOP 1 @stmt = @action+' TRIGGER '+TRIG.name+' ON '+@catalog+'.'+TAB.name+';'
                FROM [sys].[triggers] AS TRIG
                     INNER JOIN sys.tables AS TAB ON TRIG.parent_id = TAB.object_id
                WHERE trig.is_disabled = @disabled
                ORDER BY TAB.name,TRIG.name;

                SET @loop = @loop + 1;
                PRINT @stmt;
                EXEC (@stmt); --disable to print statements only
            END;
        PRINT LOWER(@action)+'d '+CONVERT(VARCHAR(5), @cnt)+' of '+CONVERT(VARCHAR(5), @totalcnt)+' triggers';
    END;
ELSE
    PRINT 'Out of '+CONVERT(VARCHAR(5), @totalcnt)+' triggers, no triggers to '+LOWER(@action);