Trigger Mass Enable / Disable

29. July 2009 by Bennett.

Pretty trivial, really:

SELECT ‘ALTER TABLE ‘ + OBJECT_NAME(PARENT_ID) + ‘ ENABLE TRIGGER ‘ + [NAME]
FROM sys.triggers    

…add a where clause if needed.  Paste the output back into the query window and modify as needed.  You can also slap some code around it to generate some dynamic SQL like this:

DECLARE @SQL VARCHAR(MAX)
SELECT @SQL=COALESCE(@SQL,”)+ ‘ALTER TABLE ‘ + OBJECT_NAME(PARENT_ID) + ‘ ENABLE TRIGGER ‘ + [NAME] + ‘; ‘
FROM sys.triggers
PRINT @SQL  

Posted in T-SQL | Print | No Comments »