Generate Commands to Enable/Disable Triggers

DROP PROCEDURE dbo.usp_GenerateEnableDisableTriggerCommands

GO

CREATE PROCEDURE dbo.usp_GenerateEnableDisableTriggerCommands

AS

/***************************************************************

Purpose: To generate commands to enable or disable all triggers

Scope depends on SQL Server version.

For SQL 2000 and earlier, only DML triggers on tables are covered.

For SQL 2005 and later, DML and DDL on both Database and Server are covered

No changes are made by this storted procedure

Author: ChillyDBA

History: 4 Jul 2012 - Initial Issue

****************************************************************/

DECLARE

@SQLVersionNumber INT

SELECT @SQLVersionNumber = CONVERT(INT, SUBSTRING(CONVERT(VARCHAR(20), SERVERPROPERTY('ProductVersion')), 1, CHARINDEX('.', CONVERT(VARCHAR(20), SERVERPROPERTY('ProductVersion')))-1))

-- if the version is before SQL 2005 (9.x.x.x)

IF @SQLVersionNumber < 9

BEGIN

-- generate code to disable all triggers (DML triggers only)

SELECT

'ALTER TABLE '

+ SCHEMA_NAME(so2.schema_id)

+ '.'

+ so2.name

+ ' DISABLE TRIGGER ALL' AS 'DISABLE Trigger Commands'

FROM sys.objects so1

INNER JOIN sys.objects so2

ON so1.parent_object_id = so2.OBJECT_ID

WHERE so1.TYPE = 'TR'

ORDER BY so2.name

-- generate code to enable all triggers (DML triggers only)

SELECT

'ALTER TABLE '

+ SCHEMA_NAME(so2.schema_id)

+ '.'

+ so2.name

+ ' ENABLE TRIGGER ALL' AS 'ENABLE Trigger Commands'

FROM sys.objects so1

INNER JOIN sys.objects so2

ON so1.parent_object_id = so2.OBJECT_ID

WHERE so1.TYPE = 'TR'

ORDER BY so2.name

END

ELSE

-- for all other versions

BEGIN

-- generate code to disable all triggers (DML and DDL triggers)

SELECT

'ALTER TABLE '

+ SCHEMA_NAME(so.schema_id)

+ '.'

+ so.name

+ ' DISABLE TRIGGER ALL' AS 'DISABLE Trigger Commands'

FROM sys.triggers st

INNER JOIN sys.objects so

ON st.parent_id = so.OBJECT_ID

WHERE st.parent_class = 1

ORDER BY so.name

-- if there are DDL triggers defined

IF EXISTS (SELECT 1 FROM sys.triggers WHERE parent_class <> 1)

BEGIN

SELECT

'DISABLE TRIGGER ALL ON DATABASE' AS 'DISABLE ALL DDL Triggers in DB'

SELECT

'DISABLE TRIGGER ALL ON ALL SERVER' AS 'DISABLE ALL DDL Triggers in Server'

END

-- generate code to enable all triggers (DML and DDL triggers)

SELECT

'ALTER TABLE '

+ SCHEMA_NAME(so.schema_id)

+ '.'

+ so.name

+ ' ENABLE TRIGGER ALL' AS 'ENABLE Trigger Commands'

FROM sys.triggers st

INNER JOIN sys.objects so

ON st.parent_id = so.OBJECT_ID

WHERE st.parent_class = 1

ORDER BY so.name

-- if there are DDL triggers defined

IF EXISTS (SELECT 1 FROM sys.triggers WHERE parent_class <> 1)

BEGIN

SELECT

'ENABLE TRIGGER ALL ON DATABASE' AS 'ENABLE ALL DDL Triggers in DB'

SELECT

'ENABLE TRIGGER ALL ON ALL SERVER' AS 'ENABLE ALL DDL Triggers in Server'

END

END