如何使用 Microsoft SQL Server 管理工作室为数据库中的所有触发器生成脚本

我想生成一个包含 SQL 的 SQL 脚本来创建数据库中存在的所有触发器。触发器是通过 SSMS 查询窗格直接添加的，因此目前除了数据库本身的触发器之外没有其他源。

我已经尝试了右键单击数据库，选择 Tasks->Generate Scripts并使用“脚本整个数据库和所有对象”选项的方法。虽然这确实为表和约束创建了 SQL 脚本，但它不为触发器生成 SQL。

我还知道我可以右键单击数据库中的每个触发器并选择 Generate SQL Script 选项，但是，目前有46个表正在审计(For Insert，Update，and Delete)。

相反，为46个表中的每一个手动生成一个插入、更新和删除触发器脚本，是否有更简单的方法来做到这一点？或者，我应该开始点击、复制和粘贴吗？

92787 次浏览
小开

这个怎么样？

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

EDIT -根据下面 jj 的评论，syscomments已被废弃，将在未来被删除。请使用前面列出的基于向导或基于脚本的解决方案:)

小开
最佳答案

数据库-> 任务-> 生成脚本-> 下一步-> 下一步

在 选择脚本选项 UI 上，在“表/视图选项”标题下，设置 脚本触发器为真。

enter image description here

enter image description here

小开

我知道这个答案已经被接受了，但是当由于某种原因 SSMS 向导不能为触发器生成脚本时(在我的案例中是 MSSQL2008R2) ，我想为这种情况提供另一种解决方案

这个解决方案是基于上面的 Dana的想法，但是使用‘ sql _ module’来提供超过4000个字符的触发器的完整代码(限制‘ 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'

右键单击结果网格，然后“将结果另存为...”保存到保留格式的文件中

小开

要为所有触发器编写脚本，可以定义存储过程:

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

去吧

小开

使用我自己的版本，结合在这里找到的答案和其他帖子(找不到原来的问题。

select OBJECT_NAME(parent_obj) AS table_name,sysobj.name AS trigger_name,
[definition],'GO'
from sys.sql_modules m
inner join sysobjects sysobj on sysobj.id=m.object_id
INNER JOIN sys.tables t ON sysobj.parent_obj = t.object_id
INNER JOIN sys.schemas s ON t.schema_id = s.schema_id
WHERE sysobj.type = 'TR' and sysobj.name like 'NAME_OF_TRIGGER'
order by sysobj.name

提交答案