So generieren Sie Skripts für alle Trigger in der Datenbank mit Microsoft SQL Server Management Studio

Datenbank-> Tasks-> Skripts Generieren -> Weiter -> Weiter

Auf Wählen Sie "Skript-Optionen UI, unter Tabelle/Ansicht-Optionen" Überschrift, Satz Script-Trigger auf True.

InformationsquelleAutor der Antwort Kapil Khandelwal

Wie über dieses?

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

BEARBEITEN - pro jj ' s Kommentar unten syscomments ist veraltet und wird in Zukunft entfallen. Bitte verwenden Sie entweder den Assistenten-basierte oder Skript-basierte Lösungen, die oben aufgeführten voran 🙂

InformationsquelleAutor der Antwort dana

Skript für alle Trigger definieren Sie die gespeicherte Prozedur:

SET ansi_nulls ON 
GO 
SET quoted_identifier ON 
GO 
-- Procedure:
--   [dbo].[Createscriptofalltriggers]
--
-- Parameter: 
--   @InclDrop bit   
--     possible values:
--     0 - Script to drop the triggers is not generated.   
--     1 - Script to drip the triggers is generated. 

ALTER PROCEDURE [dbo].[Createscriptofalltriggers]
    @InclDrop BIT =1
AS 

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 

IF @InclDrop <> 0 
  SET @InclDrop =1 

-- 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 A.NAME, 
                B.NAME, 
                B.id 
FROM   dbo.sysusers A, 
       dbo.sysobjects B, 
       dbo.syscomments C 
WHERE  A.uid = B.uid 
       AND 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 
      PRINT '/*' 

      PRINT 'The following encrypted triggers were found' 

      PRINT 'The procedure could not write the script for it' 

      SELECT DISTINCT A.NAME, 
                      B.NAME, 
                      B.id 
      FROM   dbo.sysusers A, 
             dbo.sysobjects B, 
             dbo.syscomments C 
      WHERE  A.uid = B.uid 
             AND B.type = 'Tr' 
             AND B.id = C.id 
             AND C.encrypted = 1 

      PRINT '*/' 
  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 

PRINT '-- You should run this result under dbo if your triggers belong to multiple users' 

PRINT '' 

IF @InclDrop = 1 
  BEGIN 
      PRINT '-- Dropping the Triggers' 

      PRINT '' 

      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) + Char(13) 
             + Char(10) 
      FROM   @Triggers 
  END 

PRINT '----------------------------------------------' 

PRINT '-- Creation of Triggers' 

PRINT '' 

PRINT '' 

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 
      PRINT 'SetUser N''' + @UserName + '''' + Char(13) 
            + Char(10) 

      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 

      PRINT 'Setuser' 

      FETCH next FROM ms_users INTO @UserName, @ObjID, @MaxID 
  END 

CLOSE ms_users 

PRINT 'GO' 
PRINT '------End ------' 

DEALLOCATE ms_crs_syscom 
DEALLOCATE ms_users 

GO

Wie Sie es ausführen:

SET nocount ON 
DECLARE @return_value INT 

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

GEHEN

InformationsquelleAutor der Antwort user2894913