How to Generate Scripts For All Triggers in Database Using Microsoft SQL Server Management Studio

Question

I'd like to generate an SQL Script that contains the SQL to create all of the triggers that exist in our database. The triggers were added directly via the SSMS query pane so, there is currently no source other than the trigger on the database itself.

I have already tried the method where you right-click the database, select Tasks->Generate Scripts and used the "Script Entire Database and All Objects" option. While this does create a SQL script for the tables and constraints, it does not generate SQL for the triggers.

I also understand that I can right click on each trigger in the database and select the Generate SQL Script option but, there is currently 46 tables under audit (For Insert, Update, and Delete).

Rather manually generate an insert, update, and delete trigger script for each of the 46 tables, is there an easier way to do this? Or, should I start clicking, copying, and pasting?

Solution 1

Database-> Tasks-> Generate Scripts -> Next -> Next

On Choose Script Options UI, under Table/View Options Heading, set Script Triggers to True.

Solution 2

I know the answer has been accepted already, but want to provide another solution for cases when for some reason SSMS wizard is not able to generate script for triggers (in my case it was MSSQL2008R2)

This solution is based on idea from dana above, but uses 'sql_modules' instead to provide the full code of the trigger if it exceeds 4000 chars (restriction of 'text' column of 'syscomments' view)

select [definition],'GO' from sys.sql_modules m
inner join sys.objects obj on obj.object_id=m.object_id 
 where obj.type ='TR'

Right click on the results grid and then "Save results as..." saves to file with formatting preserved

Solution 3

How about this?

select text from syscomments where text like '%CREATE TRIGGER%'

EDIT - per jj's comment below, syscomments is deprecated and will be removed in the future. Please use either the wizard-based or script-based solutions listed above moving forward :)

Solution 4

To script all triggers you can define the stored procedure:

SET ANSI_NULLS ON; 
GO 
SET QUOTED_IDENTIFIER ON; 
GO 
-- Procedure:
--   [dbo].[SYS_ScriptAllTriggers]
--
-- Parameter: 
--   @ScriptMode bit   
--     possible values:
--     0 - Script ALTER only
--     1 - Script CREATE only
--     2 - Script DROP + CREATE

ALTER PROCEDURE [dbo].[SYS_ScriptAllTriggers]
    @ScriptMode int = 0
AS 
BEGIN

    DECLARE @script TABLE (script varchar(max), id int identity (1,1))

    DECLARE 
        @SQL VARCHAR(8000), 
        @Text            NVARCHAR(4000), 
        @BlankSpaceAdded INT, 
        @BasePos         INT, 
        @CurrentPos      INT, 
        @TextLength      INT, 
        @LineId          INT, 
        @MaxID           INT, 
        @AddOnLen        INT, 
        @LFCR            INT, 
        @DefinedLength   INT, 
        @SyscomText      NVARCHAR(4000), 
        @Line            NVARCHAR(1000), 
        @UserName        SYSNAME, 
        @ObjID           INT, 
        @OldTrigID       INT; 

    SET NOCOUNT ON; 
    SET @DefinedLength = 1000; 
    SET @BlankSpaceAdded = 0; 

    SET @ScriptMode = ISNULL(@ScriptMode, 0);

    -- This Part Validated the Input parameters   
    DECLARE @Triggers TABLE (username SYSNAME NOT NULL, trigname SYSNAME NOT NULL, objid INT NOT NULL); 
    DECLARE @TrigText TABLE (objid INT NOT NULL, lineid INT NOT NULL, linetext NVARCHAR(1000) NULL); 

    INSERT INTO 
        @Triggers (username, trigname, objid) 
    SELECT DISTINCT 
        OBJECT_SCHEMA_NAME(B.id), B.name, B.id
    FROM 
        dbo.sysobjects B, dbo.syscomments C 
    WHERE 
        B.type = 'TR' AND B.id = C.id AND C.encrypted = 0; 

    IF EXISTS(SELECT C.* FROM syscomments C, sysobjects O WHERE O.id = C.id AND O.type = 'TR' AND C.encrypted = 1) 
    BEGIN 

        insert into @script select '/*'; 
        insert into @script select 'The following encrypted triggers were found'; 
        insert into @script select 'The procedure could not write the script for it'; 

        insert into 
            @script 
        SELECT DISTINCT 
            '[' + OBJECT_SCHEMA_NAME(B.id) + '].[' + B.name + ']' --, B.id 
        FROM   
            dbo.sysobjects B, dbo.syscomments C 
        WHERE  
            B.type = 'TR' AND B.id = C.id AND C.encrypted = 1; 

        insert into @script select '*/'; 
    END; 

    DECLARE ms_crs_syscom CURSOR LOCAL forward_only FOR 
    SELECT 
        T.objid, C.text
    FROM   
        @Triggers T, dbo.syscomments C 
    WHERE  
        T.objid = C.id 
    ORDER  BY T.objid, 
        C.colid 
    FOR READ ONLY; 

    SELECT @LFCR = 2; 
    SELECT @LineId = 1; 

    OPEN ms_crs_syscom; 

    SET @OldTrigID = -1; 

    FETCH NEXT FROM ms_crs_syscom INTO @ObjID, @SyscomText; 

    WHILE @@fetch_status = 0 
    BEGIN 

        SELECT @BasePos = 1; 
        SELECT @CurrentPos = 1; 
        SELECT @TextLength = LEN(@SyscomText); 

        IF @ObjID <> @OldTrigID 
        BEGIN 
            SET @LineID = 1; 
            SET @OldTrigID = @ObjID; 
        END; 

        WHILE @CurrentPos != 0 
        BEGIN 
            --Looking for end of line followed by carriage return         
            SELECT @CurrentPos = CHARINDEX(CHAR(13) + CHAR(10), @SyscomText, @BasePos); 

            --If carriage return found         
            IF @CurrentPos != 0 
            BEGIN 

                WHILE ( ISNULL(LEN(@Line), 0) + @BlankSpaceAdded + @CurrentPos - @BasePos + @LFCR ) > @DefinedLength 
                BEGIN 
                    SELECT @AddOnLen = @DefinedLength - (ISNULL(LEN(@Line), 0) + @BlankSpaceAdded ); 

                    INSERT 
                        @TrigText 
                    VALUES 
                        ( @ObjID, @LineId, ISNULL(@Line, N'') + ISNULL(SUBSTRING(@SyscomText, @BasePos, @AddOnLen), N'')); 

                    SELECT 
                        @Line = NULL, 
                        @LineId = @LineId + 1, 
                        @BasePos = @BasePos + @AddOnLen, 
                        @BlankSpaceAdded = 0; 
                END; 

                SELECT @Line = ISNULL(@Line, N'') + ISNULL(SUBSTRING(@SyscomText, @BasePos, @CurrentPos - @BasePos + @LFCR), N''); 

                SELECT @BasePos = @CurrentPos + 2; 

                INSERT 
                    @TrigText 
                VALUES
                    ( @ObjID, @LineId, @Line ); 

                SELECT @LineId = @LineId + 1; 

                SELECT @Line = NULL; 
            END; 
            ELSE 
            --else carriage return not found         
            BEGIN 
                IF @BasePos <= @TextLength 
                BEGIN 
                    /*If new value for @Lines length will be > then the         
                    **defined length         
                    */ 
                    WHILE ( ISNULL(LEN(@Line), 0) + @BlankSpaceAdded + @TextLength - @BasePos + 1 ) > @DefinedLength 
                    BEGIN 
                        SELECT @AddOnLen = @DefinedLength - ( ISNULL(LEN(@Line), 0 ) + @BlankSpaceAdded ); 

                        INSERT 
                            @TrigText 
                        VALUES 
                            ( @ObjID, @LineId, ISNULL(@Line, N'') + ISNULL(SUBSTRING(@SyscomText, @BasePos, @AddOnLen), N'')); 

                        SELECT 
                            @Line = NULL, 
                            @LineId = @LineId + 1, 
                            @BasePos = @BasePos + @AddOnLen, 
                            @BlankSpaceAdded = 0; 
                    END; 

                    SELECT @Line = ISNULL(@Line, N'') + ISNULL(SUBSTRING(@SyscomText, @BasePos, @TextLength - @BasePos+1 ), N''); 

                    IF LEN(@Line) < @DefinedLength AND CHARINDEX(' ', @SyscomText, @TextLength + 1) > 0 
                    BEGIN 
                        SELECT 
                            @Line = @Line + ' ', 
                            @BlankSpaceAdded = 1; 
                    END; 
                END; 
            END; 
        END; 

        FETCH NEXT FROM ms_crs_syscom INTO @ObjID, @SyscomText; 
    END; 

    IF @Line IS NOT NULL 
        INSERT 
            @TrigText 
        VALUES
            ( @ObjID, @LineId, @Line ); 

    CLOSE ms_crs_syscom; 

    insert into @script select '-- You should run this result under dbo if your triggers belong to multiple users'; 
    insert into @script select ''; 

    IF @ScriptMode = 2 
    BEGIN 

        insert into @script select '-- Dropping the Triggers'; 
        insert into @script select ''; 

        insert into @script 
        SELECT 
            'IF EXISTS(SELECT * FROM sysobjects WHERE id = OBJECT_ID(''[' + username + '].[' + trigname + ']'')'
            + ' AND ObjectProperty(OBJECT_ID(''[' + username + '].[' + trigname + ']''), ''ISTRIGGER'') = 1)'
            + ' DROP TRIGGER [' + username + '].[' + trigname +']' + CHAR(13) + CHAR(10) 
            + 'GO' + CHAR(13) + CHAR(10)
        FROM   
            @Triggers; 
    END; 

    IF @ScriptMode = 0
    BEGIN   
        update 
            @TrigText 
        set 
            linetext = replace(linetext, 'CREATE TRIGGER', 'ALTER TRIGGER') 
        WHERE 
            upper(left(replace(ltrim(linetext), char(9), ''), 14)) = 'CREATE TRIGGER' 
    END

    insert into @script select '----------------------------------------------'; 
    insert into @script select '-- Creation of Triggers'; 
    insert into @script select ''; 
    insert into @script select ''; 

    DECLARE ms_users CURSOR LOCAL forward_only FOR 
    SELECT 
        T.username, 
        T.objid, 
        MAX(D.lineid) 
    FROM   
        @Triggers T, 
        @TrigText D 
    WHERE  
        T.objid = D.objid 
    GROUP BY    
        T.username, 
        T.objid 
    FOR READ ONLY; 

    OPEN ms_users; 

    FETCH NEXT FROM ms_users INTO @UserName, @ObjID, @MaxID; 

    WHILE @@fetch_status = 0 
    BEGIN 

        insert into @script select 'setuser N''' + @UserName + '''' + CHAR(13) + CHAR(10); 

        insert into @script 
        SELECT 
            '-- Text of the Trigger' = 
            CASE lineid 
                WHEN 1 THEN 'GO' + CHAR(13) + CHAR(10) + linetext 
                WHEN @MaxID THEN linetext + 'GO' 
                ELSE linetext 
            END 
        FROM   
            @TrigText 
        WHERE  
            objid = @ObjID 
        ORDER  
            BY lineid; 

        insert into @script select 'setuser'; 

        FETCH NEXT FROM ms_users INTO @UserName, @ObjID, @MaxID; 
    END; 

    CLOSE ms_users; 

    insert into @script select 'GO'; 
    insert into @script select '------End ------'; 

    DEALLOCATE ms_crs_syscom; 
    DEALLOCATE ms_users; 

    select script from @script order by id

END

How to execute it:

SET nocount ON 
DECLARE @return_value INT 

EXEC @return_value = [dbo].[SYS_ScriptAllTriggers] @InclDrop = 1 
SELECT 'Return Value' = @return_value 

GO

View more solutions

Comments

[Grasshopper]

This did appear to pull back the triggers but, everything was in one row of the table and it wasn't formatted. KPL's answer did the trick for me. Thanks for the answer though. I was not aware of the syscomments table.

[dana]

Interesting. I had not thought of that, but it is a good idea :)

[MAW74656]

-1 Results do not contain entire text of my triggers.

[dana]

@MAW74656 - Uncle... I guess my answer was intended to be more of a quick and dirty way to find triggers w/o using a GUI. The accepted answer probably works best for most folk. However, it is sometimes nice to have an low-level alternative. It looks like Igor has taken things a step further than I did, and at this point I would probably go with what he has. However, I'll leave up my answer for reference.

[David Russell]

In SSMS2012, the Options dialog is found by clicking Advanced in the Set Scripting Options step.

[jj.]

Quick note: syscomments is deprecated (will be removed sometime after SQL 2014), Microsoft recommends using sys.sql_modules

[Edwin Stoteler]

How do you use this if you wish to generate ONLY the triggers?

[Stefan Steiger]

SELECT sch.name AS schema_name ,parent.name AS table_name ,obj.name AS trigger_name ,m.[definition] AS trigger_definition FROM sys.sql_modules AS m INNER JOIN sys.objects AS obj ON obj.object_id=m.object_id INNER JOIN sys.objects AS parent ON parent.object_id = obj.parent_object_id INNER JOIN sys.schemas AS sch ON sch.schema_id = parent.schema_id WHERE obj.type ='TR'