HawkeSoft SQL Tips and Tricks - Part 4 - Script All Triggers in a Database

Triggers in SQL Server are often treated as being directly tied to the table they fire off of. This makes sense in most scenarios but sometimes they need to be treated more as code objects. Most SQL tools make scripting triggers out a sub function of scripting tables. This script allows you to script out all triggers on a given database without concerning yourself with the tables (SQL Server 2005, large script).

Make sure to run this script in TEXT output mode and have a large buffer in your output window to handle large triggers.

-- USE the database you wish to script all triggers out of
USE [Database]
GO

SET NOCOUNT ON
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE Procedure [dbo].[DBAGenerateTriggerScript]
(
@TriggerName varchar(255)
)
AS
/******************************************************************************
** Name: DBAGenerateTriggerScript
** Desc: Generates the DROP and CREATE statements for the given @TriggerName.
** IMPORTANT: Run this script in TEXT output mode
** Make sure you have a large buffer in your output window to handle large triggers
**
** Auth: ghawkes
** Date: 07/07/2008
*******************************************************************************/

SET NOCOUNT ON

-- Check to make sure the stored procedure exists
IF(NOT EXISTS(SELECT object_id
FROM sys.all_objects SO
WHERE SO.type = N'TR'
AND (SO.name = @TriggerName AND SCHEMA_NAME(SO.schema_id )= N'dbo')))
BEGIN
PRINT '''' + @TriggerName + ''' is not a valid trigger on the ''' + DB_NAME() + ''' database.'
RETURN
END

DECLARE @NewLine char(2)
SET @NewLine = CHAR(13) + CHAR(10)

-- Generate the comment header
SELECT '/***** Object: Trigger [dbo].[' + @TriggerName + '] Script Date: ' + CONVERT(varchar, GETDATE(), 100) + ' *****/' + @NewLine + @NewLine

UNION ALL

-- Generate the DROP statement
SELECT 'IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N''[dbo].[' + @TriggerName + ']'') AND type in (N''TR''))' + @NewLine +
'DROP TRIGGER [dbo].[' + @TriggerName + ']' + @NewLine + 'GO' + @NewLine + @NewLine

UNION ALL

SELECT 'SET ANSI_NULLS ON' + @NewLine +
'GO' + @NewLine +
'SET QUOTED_IDENTIFIER ON' + @NewLine +
'GO'

UNION ALL

-- Generate the CREATE statement
SELECT
ISNULL(SM.definition, SSM.definition)
FROM sys.all_objects SO
LEFT OUTER JOIN sys.sql_modules AS SM ON SM.object_id = SO.object_id
LEFT OUTER JOIN sys.system_sql_modules AS SSM ON SSM.object_id = SO.object_id
WHERE SO.type = N'TR'
AND (SO.name = @TriggerName AND SCHEMA_NAME(SO.schema_id )= N'dbo')

UNION ALL

SELECT @NewLine + 'GO' + @NewLine
GO

-- Invoke the trigger script creation procedure for all triggers on the database
DECLARE @NewLine char(2)
SET @NewLine = CHAR(13) + CHAR(10)

-- Generate the USE statement
SELECT 'USE [' + DB_NAME() + ']' + @NewLine + 'GO' + @NewLine + @NewLine
DECLARE @SQL varchar(MAX)
SET @SQL = ''
SELECT @SQL = @SQL + 'EXEC DBAGenerateTriggerScript ''' + ST.name + '''' + ' '
FROM sys.triggers ST

EXEC(@SQL)
GO

-- Drop the trigger script creation procedure
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[DBAGenerateTriggerScript]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[DBAGenerateTriggerScript]
GO