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();
}
}
Lianja developer 