MS SQL: Generate script for triggers

Management Studio Wizard is not useful, better to run script and copy print output to execute it.

Code:
SET NOCOUNT ON

CREATE TABLE #triggerFullText ([TriggerName] VARCHAR(500), [Text] VARCHAR(MAX))
CREATE TABLE #triggerLines ([Text] VARCHAR(MAX))

DECLARE @triggerName VARCHAR(500)
DECLARE @fullText VARCHAR(MAX)
DECLARE @fullText2 VARCHAR(MAX)
DECLARE @fullText3 VARCHAR(MAX)

SELECT @triggerName = MIN(name)
FROM sys.triggers
declare @apostrophe char(1),@s13 char(1),@s10 char(1)
set @apostrophe=char(39)
set @s13=char(13)
set @s10=char(10)
declare @le2 bigint, @le3 bigint, @cure bigint

DECLARE @CurrentEnd BIGINT; /* track the length of the next substring */
DECLARE @offset tinyint; /*tracks the amount of offset needed */

WHILE @triggerName IS NOT NULL 
BEGIN
	SET @fullText = 
	'IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N''[dbo].['+@triggerName+']'') AND type in (N''TR''))'+ Char(13)+ CHAR(10)+
	'DROP TRIGGER [dbo].['+@triggerName+']'+ Char(13)+ CHAR(10)+
	'GO'+ Char(13)+ CHAR(10)

	SET @fullText = @fullText+
	'SET ANSI_NULLS ON'+ Char(13)+ CHAR(10)+
	'GO'+ Char(13)+ CHAR(10)

	SET @fullText = @fullText+
	'SET QUOTED_IDENTIFIER ON'+ Char(13)+ CHAR(10)+
	'GO'+ Char(13)+ CHAR(10)

	SET @fullText = @fullText+
	'IF  NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N''[dbo].['+@triggerName+']'') AND type in (N''TR''))'+ Char(13)+ CHAR(10)+
	'BEGIN'+ Char(13)+ CHAR(10)+
	'execute dbo.sp_executesql @statement = N'''+ Char(13)+ CHAR(10)
	begin
	    PRINT @fullText
    end

    SET @fullText = NULL
	
	begin
    INSERT INTO #triggerLines 
    EXEC sp_helptext @triggerName

    --sp_helptext gives us one row per trigger line
    --here we join lines into one variable
    
    --changing ' to ''
    SELECT @fullText = ISNULL(@fullText, '') + replace([TEXT],@apostrophe,@apostrophe+@apostrophe)
    FROM #triggerLines where len(ltrim(rtrim([TEXT])))>2
	-- >2 for skipping empty rows

    --adding "GO" for ease of copy paste execution
    SET @fullText = @fullText + '''' + Char(13)+ CHAR(10)
    SET @fullText = @fullText + 'END' + Char(13)+ CHAR(10)
    SET @fullText = @fullText + Char(13)+ CHAR(10)+ 'GO' + Char(13)+ CHAR(10)+
    N'/**********END TRIGGER [dbo].['+@triggerName+N']***********/'+ Char(13)+ CHAR(10)+ Char(13)+ CHAR(10)
	end

	set @fullText=replace(ltrim(rtrim(@fullText)),@s13+@s10+@s13+@s10,@s13+@s10)
	if len(@fullText)>8000	begin
		-- problem with PRINT more than 8000
		WHILE LEN(@fullText) > 1
		BEGIN
		   SET @Cure =CHARINDEX(CHAR(13), @fullText)
			IF CHARINDEX(CHAR(13), @fullText) between 1 AND 8000
			BEGIN
				   SET @CurrentEnd =  CHARINDEX(CHAR(13), @fullText) -1
				   set @offset = 2
			END
			ELSE
				begin
				    SET @CurrentEnd = 8000
					set @offset = 1
				END
				set @fullText2=SUBSTRING(@fullText, 1, @CurrentEnd)
				set @le2=len(@fullText2)
				if ascii(left(@fullText2,1))=10 begin
					print substring(@fullText2,2,@le2)
				end else begin
					print @fullText2
				end
				
				set @fullText3=SUBSTRING(@fullText, @CurrentEnd+@offset, LEN(@fullText))
				set @le3=len(@fullText3)
			set @fullText = SUBSTRING(@fullText, @CurrentEnd+@offset, LEN(@fullText))   
		END /*End While loop*/
	end else begin
		print @fullText	
	end

    --accumulating result for future manipulations
    INSERT INTO #triggerFullText([TriggerName], [Text])
    VALUES(@triggerName, @fullText)

    --iterating over next trigger
    SELECT @triggerName = MIN(name)
    FROM sys.triggers
    WHERE name > @triggerName

    SET @fullText = NULL

    TRUNCATE TABLE #triggerLines
END

DROP TABLE #triggerFullText
DROP TABLE #triggerLines

Credit to: https://stackoverflow.com/questions/14413573/generate-script-for-triggers-only-using-script-wizard

https://stackoverflow.com/questions/7850477/how-to-print-varcharmax-using-print-statement

and: https://stackoverflow.com/questions/31052934/drop-trigger-if-exists-and-create

Improvements: dropping the triggers and avoiding empty lines in the script and printing more strings than 8000 chars

Usage:

Resulting code is copied into a sql file which is then read in C#:

Code:
string[] cmds1 = null;
using (StreamReader sr1 = new StreamReader(@"trigg.sql")
{
    cmds1 = sr1.ReadToEnd().Split(new string[] { "GO\r" }, StringSplitOptions.RemoveEmptyEntries);
    sr1.Close();
}
int rowseaffected=0;
if (cmds1 != null)
{
    cmd.CommandTimeout = 900;
    for (int i = 0; i < cmds1.Length; i++)
    {
        cmd.CommandText = cmds1[i];
        rowseaffected = cmd.ExecuteNonQuery();
    }
}
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s