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

HawkeSoft SQL Tips and Tricks - Part 1 - Writing SQL with SQL

This is the first in a series of posts describing SQL tips and tricks for SQL Server 2000/2005/2008. We at HawkeSoft hope you enjoy this series, and as always, we welcome your feedback!

Writing SQL with SQL:
Want to know the number of rows in every table in your database? No problem, until you realize your database has hundreds of tables. Try the code sample below for a quick dynamic SQL solution (SQL Server 2005).

DECLARE @SQL varchar(max)
SET @SQL = ''

SELECT @SQL = @SQL + 'SELECT COUNT(1) AS [' + ST.name + ' Count] FROM ' + ST.name + ' ' FROM sys.tables ST

--SELECT @SQL
EXEC(@SQL)

As you can see this approach is very powerful and can be applied in many different situations such as database maintenance commands, code re-compilations, updating statistics, etc...

If this type of code interests you should also check out the undocumented stored procedures sp_MSforeachtable and sp_MSforeachdb.

Remember, when dealing with code that has the potential to update several objects at once in your database ALWAYS make sure it is going to do what you anticipate, and consider the performance impacts. Happy coding!