1

I have a few databases involved in replication as subscribers and they have triggers and are used by a different set of applications.

Every time I need to reinitialise those database I need to script the triggers, permissions and indexes, apply the snapshot and then re-apply those triggers, indexes and permissions.

Is there a script that I can use to script all the triggers of a current database?

2

This seems a whole lot simpler than what you have:

CREATE TABLE #tmp
(
  db          sysname,
  sch         sysname,
  obj         sysname,
  name        sysname,
  is_disabled bit,
  def         nvarchar(max)
);
GO

INSERT #tmp SELECT DB_NAME(), 
  s.name, o.name, t.name, 
  t.is_disabled, m.definition
FROM sys.triggers AS t
INNER JOIN sys.sql_modules AS m
ON t.object_id = m.object_id
INNER JOIN sys.objects AS o
ON t.parent_id = o.object_id
INNER JOIN sys.schemas AS s
ON o.schema_id = s.schema_id
WHERE parent_class = 1;

When you're ready to create them again:

DECLARE @sql nvarchar(max) = N'';

SELECT @sql += def 
  + CHAR(13) + CHAR(10) + N'GO' 
  + CHAR(13) + CHAR(10) 
FROM #tmp;

SELECT @sql += N'DISABLE TRIGGER ' 
  + QUOTENAME(sch) + N'.' + QUOTENAME(name)
  + N' ON ' 
  + QUOTENAME(sch) + N'.' + QUOTENAME(obj) + N';'
FROM #tmp WHERE is_disabled = 1;

PRINT @sql;
-- EXEC sys.sp_executesql @sql;

Yes, you won't be able to validate the entire script is there, because SSMS won't output the entire value of @sql. See this post for workarounds.

Also, I don't know why you need the database as part of the dynamic script if you always know the database you're affecting, but there's a much safer way to make the database name dynamic without concatenating values and inviting SQL injection:

DECLARE @db sysname = N'AdventureWorks';

DECLARE @exec nvarchar(max), @sql nvarchar(max);

SET @exec = QUOTENAME(@db) + N'.sys.sp_executesql';

SET @sql = N'SELECT DB_NAME();';

EXEC @exec @sql;
0

Please find below a script that has helped me a lot:

-- script all triggers of the current database
--marcelo miorelli
--17-april-2018
DECLARE @CHECK_IF_TRIGGER_EXISTS BIT = 1

SET NOCOUNT ON
SET DEADLOCK_PRIORITY LOW
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

IF OBJECT_ID('tempdb..#Radhe') IS NOT NULL
   BEGIN 
      DROP TABLE #RADHE
   END 

CREATE TABLE #Radhe(
    DB sysname not null,
    parent_name nvarchar(600) not null,
    object_id int not null,
    trigger_name sysname not null,
    is_disabled bit,
    i int not null identity(1,1),
    [trigger_definition] NVARCHAR(MAX) not null
);

DECLARE @object_id int;
DECLARE @SQL nvarchar(max);
DECLARE @theSQL nvarchar(max);
declare @DB sysname
declare @parent_name nvarchar(600)
declare @trigger_name sysname
declare @is_disabled bit


 select @DB = db_name(db_id())
 --select @DB 



    SELECT @SQL =
     '-------------------------------------------------------------------------------------------------------------------
                DECLARE 
                  @olddelim nvarchar(32) = char(13) + Char(10),
                  @newdelim nchar(1)     = NCHAR(9999); -- pencil (✏)

                SELECT the_db_name = quotename(db_name(db_id()))
                      ,parent_name  = @parent_name 
                      ,the_object_id = @obj_id
                      ,trigger_name  = @trigger_name
                      ,is_disabled   = @is_disabled
                      ,trigger_definition = [Value] 
                FROM master.dbo.splitstring(OBJECT_DEFINITION(@obj_id), @olddelim);
     -------------------------------------------------------------------------------------------------------------------';

-- PRINT @SQL




BEGIN TRY

            DECLARE the_triggers CURSOR STATIC LOCAL FORWARD_ONLY READ_ONLY 
            FOR
            SELECT 
             object_id=s.object_id
        ,parent_name  = QUOTENAME(OBJECT_SCHEMA_NAME(s.parent_id)) + '.' + QUOTENAME(OBJECT_NAME(s.parent_id))
            ,trigger_name = QUOTENAME(OBJECT_SCHEMA_NAME(s.object_id)) + '.' + QUOTENAME(OBJECT_NAME(s.object_id))
            ,s.is_disabled
            FROM sys.triggers s
            WHERE 1=1


            OPEN the_triggers;
            FETCH NEXT FROM the_triggers 
            INTO @object_id,@parent_name,@trigger_name,@is_disabled;

            WHILE @@FETCH_STATUS = 0
            BEGIN

                SET @theSQL = 'EXEC ' + QUOTENAME(@DB) + 
                                    '.sys.sp_executesql @SQL'     + CHAR(10) +
                                    ',N''@obj_id int,@parent_name nvarchar(600),@trigger_name sysname,@is_disabled bit'',' + CHAR(10) + 
                                    '''' + CAST (@object_id as nvarchar)      + '''' +  ',' + 
                                    '''' + @parent_name   + '''' +  ',' + 
                                    '''' + @trigger_name  + '''' +  ',' + 
                                    '''' + CAST (COALESCE(@is_disabled,0) as nvarchar) + 
                                    '''' +';'    + CHAR(10)

                -------------------------------------------                        
                -- when @CHECK_IF_TRIGGER_EXISTS is on
                -- add code that checks whether the trigger exists
                -- and if it does drop it
                -------------------------------------------

                if @CHECK_IF_TRIGGER_EXISTS = 1 
                   BEGIN

                     INSERT INTO #Radhe(DB,parent_name,object_id,trigger_name,is_disabled,trigger_definition) values
                                 (QUOTENAME(db_name()),@parent_name,@object_id,@trigger_name,@is_disabled,'GO')

                     INSERT INTO #Radhe(DB,parent_name,object_id,trigger_name,is_disabled,trigger_definition) values
                                 (QUOTENAME(db_name()),@parent_name,@object_id,@trigger_name,@is_disabled,'use ' + QUOTENAME(db_name()))

                     INSERT INTO #Radhe(DB,parent_name,object_id,trigger_name,is_disabled,trigger_definition) values
                                 (QUOTENAME(db_name()),@parent_name,@object_id,@trigger_name,@is_disabled,'GO')

                     INSERT INTO #Radhe(DB,parent_name,object_id,trigger_name,is_disabled,trigger_definition) values
                                 (QUOTENAME(db_name()),@parent_name,@object_id,@trigger_name,@is_disabled,'if OBJECT_ID('+ '''' + @trigger_name + '''' + ') is not null')

                     INSERT INTO #Radhe(DB,parent_name,object_id,trigger_name,is_disabled,trigger_definition) values
                                 (QUOTENAME(db_name()),@parent_name,@object_id,@trigger_name,@is_disabled,'         drop trigger '+ @trigger_name + ' ')

                     INSERT INTO #Radhe(DB,parent_name,object_id,trigger_name,is_disabled,trigger_definition) values
                                 (QUOTENAME(db_name()),@parent_name,@object_id,@trigger_name,@is_disabled,'GO')


                   END 

                -------------------------------------------                        
                -- do the insert here
                -- the trigger source code
                -------------------------------------------

                --print @theSQL

                INSERT INTO #Radhe(DB,parent_name,object_id,trigger_name,is_disabled,trigger_definition)
                EXEC sys.sp_executesql @theSQL
                    , N'@SQL nvarchar(max) '
                    , @SQL = @sql

                -- add a GO after the trigger definition
                INSERT INTO #Radhe(DB,parent_name,object_id,trigger_name,is_disabled,trigger_definition) values
                            (QUOTENAME(db_name()),@parent_name,@object_id,@trigger_name,@is_disabled,'GO')

                -- fetch the next trigger
                FETCH NEXT FROM the_triggers 
                INTO @object_id,@parent_name,@trigger_name,@is_disabled;

            END

            ------------------------------------------- 
            BEGIN TRY
                --clean it up    
                CLOSE the_triggers;
                DEALLOCATE the_triggers;
            END TRY
            BEGIN CATCH
                --do nothing
            END CATCH
            ------------------------------------------- 

END TRY

BEGIN CATCH

            ------------------------------------------- 
            BEGIN TRY
                --clean it up    
                CLOSE the_triggers;
                DEALLOCATE the_triggers;
            END TRY
            BEGIN CATCH
                --do nothing
            END CATCH
            ------------------------------------------- 

    DECLARE @ERRORMESSAGE    NVARCHAR(512),
            @ERRORSEVERITY   INT,
            @ERRORNUMBER     INT,
            @ERRORSTATE      INT,
            @ERRORPROCEDURE  SYSNAME,
            @ERRORLINE       INT,
            @XASTATE         INT

    SELECT
            @ERRORMESSAGE     = ERROR_MESSAGE(),
            @ERRORSEVERITY    = ERROR_SEVERITY(),
            @ERRORNUMBER      = ERROR_NUMBER(),
            @ERRORSTATE       = ERROR_STATE(),
            @ERRORPROCEDURE   = ERROR_PROCEDURE(),
            @ERRORLINE        = ERROR_LINE()

    SET @ERRORMESSAGE = 
    (
    SELECT                    CHAR(13) +
      'Message:'         +    SPACE(1) + @ErrorMessage                           + SPACE(2) + CHAR(13) +
      'Error:'           +    SPACE(1) + CONVERT(NVARCHAR(50),@ErrorNumber)      + SPACE(1) + CHAR(13) +
      'Severity:'        +    SPACE(1) + CONVERT(NVARCHAR(50),@ErrorSeverity)    + SPACE(1) + CHAR(13) +
      'State:'           +    SPACE(1) + CONVERT(NVARCHAR(50),@ErrorState)       + SPACE(1) + CHAR(13) +
      'Routine_Name:'    +    SPACE(1) + COALESCE(@ErrorProcedure,'')            + SPACE(1) + CHAR(13) +
      'Line:'            +    SPACE(1) + CONVERT(NVARCHAR(50),@ErrorLine)        + SPACE(1) + CHAR(13) +
      'Executed As:'     +    SPACE(1) + SYSTEM_USER + SPACE(1)                             + CHAR(13) +
      'Database:'        +    SPACE(1) + DB_NAME() + SPACE(1)                               + CHAR(13) +
      'OSTime:'          +    SPACE(1) + CONVERT(NVARCHAR(25),CURRENT_TIMESTAMP,121)        + CHAR(13) 
    )

    --We can also save the error details to a table for later reference here.
    RAISERROR (@ERRORMESSAGE,16,1)

END CATCH

SELECT * FROM #RADHE

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.