반응형
Microsoft SQL Server Management Studio를 사용하여 데이터베이스의 모든 트리거에 대한 스크립트를 생성하는 방법
데이터베이스에 존재하는 모든 트리거를 생성하기 위해 SQL이 포함 된 SQL 스크립트를 생성하고 싶습니다. 트리거는 SSMS 쿼리 창을 통해 직접 추가되었으므로 현재 데이터베이스 자체에는 트리거 이외의 소스가 없습니다.
데이터베이스를 마우스 오른쪽 단추로 클릭 Tasks->Generate Scripts하고 "전체 데이터베이스 및 모든 개체 스크립트"옵션을 선택 하여 사용 하는 방법을 이미 시도했습니다 . 이렇게하면 테이블 및 제약 조건에 대한 SQL 스크립트가 생성되지만 트리거에 대한 SQL은 생성되지 않습니다.
또한 데이터베이스의 각 트리거를 마우스 오른쪽 단추로 클릭하고 SQL 스크립트 생성 옵션을 선택할 수 있지만 현재 감사중인 테이블이 46 개 (삽입, 업데이트 및 삭제 용)라는 것을 알고 있습니다.
46 개 테이블 각각에 대해 삽입, 업데이트 및 삭제 트리거 스크립트를 수동으로 생성하는 대신 더 쉬운 방법이 있습니까? 아니면 클릭, 복사 및 붙여 넣기를 시작해야합니까?
데이터베이스-> 작업-> 스크립트 생성-> 다음-> 다음
에 스크립트 옵션 선택 테이블 / 뷰 옵션 제목 아래, 설정, UI를 True로 스크립트 트리거 .
답변이 이미 수락되었음을 알고 있지만 어떤 이유로 SSMS 마법사가 트리거에 대한 스크립트를 생성 할 수없는 경우에 대한 또 다른 솔루션을 제공하고 싶습니다 (제 경우에는 MSSQL2008R2였습니다).
이 솔루션은 위의 dana의 아이디어를 기반으로 하지만 4000자를 초과하는 경우 트리거의 전체 코드를 제공하기 위해 대신 'sql_modules'를 사용합니다 ( 'syscomments'보기의 'text'열 제한).
select [definition],'GO' from sys.sql_modules m
inner join sys.objects obj on obj.object_id=m.object_id
where obj.type ='TR'
결과 그리드를 마우스 오른쪽 버튼으로 클릭 한 다음 "다른 이름으로 결과 저장 ..."은 서식이 유지 된 파일에 저장됩니다.
이것은 어떤가요?
select text from syscomments where text like '%CREATE TRIGGER%'
편집 -아래 jj의 댓글에 따라 syscomments더 이상 사용되지 않으며 향후 제거됩니다. 위에 나열된 마법사 기반 또는 스크립트 기반 솔루션을 사용하십시오.
모든 트리거를 스크립팅하려면 저장 프로 시저를 정의 할 수 있습니다.
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
실행 방법 :
SET nocount ON
DECLARE @return_value INT
EXEC @return_value = [dbo].[SYS_ScriptAllTriggers] @InclDrop = 1
SELECT 'Return Value' = @return_value
가다
반응형