How to Generate Scripts For All Triggers in Database Using Microsoft SQL Server Management Studio
SqlSql Server-2008TriggersSql Problem Overview
I'd like to generate an SQL Script that contains the SQL to create all of the triggers that exist in our database. The triggers were added directly via the SSMS query pane so, there is currently no source other than the trigger on the database itself.
I have already tried the method where you right-click the database, select Tasks->Generate Scripts and used the "Script Entire Database and All Objects" option. While this does create a SQL script for the tables and constraints, it does not generate SQL for the triggers.
I also understand that I can right click on each trigger in the database and select the Generate SQL Script option but, there is currently 46 tables under audit (For Insert, Update, and Delete).
Rather manually generate an insert, update, and delete trigger script for each of the 46 tables, is there an easier way to do this? Or, should I start clicking, copying, and pasting?
Sql Solutions
Solution 1 - Sql
Database-> Tasks-> Generate Scripts -> Next -> Next
On Choose Script Options UI, under Table/View Options Heading, set Script Triggers to True.
Solution 2 - Sql
I know the answer has been accepted already, but want to provide another solution for cases when for some reason SSMS wizard is not able to generate script for triggers (in my case it was MSSQL2008R2)
This solution is based on idea from [dana][1] above, but uses 'sql_modules' instead to provide the full code of the trigger if it exceeds 4000 chars (restriction of 'text' column of 'syscomments' view)
select [definition],'GO' from sys.sql_modules m
inner join sys.objects obj on obj.object_id=m.object_id
where obj.type ='TR'
Right click on the results grid and then "Save results as..." saves to file with formatting preserved
[1]: https://stackoverflow.com/users/315689/dana "dana"
Solution 3 - Sql
How about this?
select text from syscomments where text like '%CREATE TRIGGER%'
EDIT - per jj's comment below, syscomments is deprecated and will be removed in the future. Please use either the wizard-based or script-based solutions listed above moving forward :)
Solution 4 - Sql
To script all triggers you can define the stored procedure:
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
How to execute it:
SET nocount ON
DECLARE @return_value INT
EXEC @return_value = [dbo].[SYS_ScriptAllTriggers] @InclDrop = 1
SELECT 'Return Value' = @return_value
GO
Solution 5 - Sql
Using my own version with the combination of answer found in here and other post(can't find the original quetion.
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