Как сгенерировать скрипты для всех триггеров в базе данных с помощью Microsoft SQL Server Management Studio

Я хотел бы создать SQL Script, который содержит SQL, чтобы создать все триггеры, существующие в нашей базе данных. Триггеры были добавлены непосредственно через панель запросов SSMS, поэтому в настоящее время нет источника, кроме триггера в самой базе данных.

Я уже пробовал метод, в котором вы щелкните правой кнопкой мыши базу данных, выберите Tasks->Generate Scripts и используйте параметр "Script Целая база данных и все объекты". Хотя это создает SQL Script для таблиц и ограничений, он не генерирует SQL для триггеров.

Я также понимаю, что я могу щелкнуть правой кнопкой мыши по каждому триггеру в базе данных и выбрать опцию Generate SQL Script, но в настоящее время существует 46 таблиц под аудитом (для вставки, обновления и удаления).

Скорее вручную создайте триггер для вставки, обновления и удаления Script для каждой из 46 таблиц, есть ли более простой способ сделать это? Или, если я начну нажимать, копировать и вставлять?

Ответ 1

База данных- > Задачи- > Сгенерировать скрипты → Далее → Далее

В Выберите Script Параметры пользовательский интерфейс в разделе "Параметры таблицы/просмотра", установите Script Триггеры на True.

Ответ 2

Я знаю, что ответ уже принят, но хочу предоставить другое решение для случаев, когда по какой-то причине мастер SSMS не может генерировать script для триггеров (в моем случае это был MSSQL2008R2)

Это решение основано на идее dana выше, но вместо этого использует 'sql_modules', чтобы предоставить полный код триггера если оно превышает 4000 символов (ограничение столбца "текст" представления "syscomments" )

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

Щелкните правой кнопкой мыши по сетке результатов, а затем "Сохранить результаты как..." сохранит файл с сохраненным форматированием.

Ответ 3

Как насчет этого?

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

EDIT - за jj комментарий ниже, syscomments устарел и будет удален в будущем. Используйте перечисленные выше решения на основе мастера или script:)

Ответ 4

В script все триггеры вы можете определить хранимую процедуру:

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 
                                 [email protected] + 
                                 @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 
                                       [email protected]+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

Как выполнить его:

SET nocount ON 
DECLARE @return_value INT 

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

ГО

Ответ 5

Использование syscomments может не всегда работать. Текстовый столбец syscomments ограничен 4000 символами. Триггер больше этого будет усечен. Возможно, вы не сможете полностью просмотреть его.

Ответ 6

Собственно, если вы посмотрите на таблицу sys.comments, у нее есть поле colid, которое является числовым столбцом; если оно больше максимального, оно будет иметь число > 1. Если вы должны выбрать один s > 1 colid и скопировать оба (если было 2 записи), это даст вам весь триггер!