Please find below a script that has helped me a lot:
-- script all triggers of the current database
--marcelo miorelli
--17-april-2018
DECLARE @CHECK_IF_TRIGGER_EXISTS BIT = 1
SET NOCOUNT ON
SET DEADLOCK_PRIORITY LOW
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
IF OBJECT_ID('tempdb..#Radhe') IS NOT NULL
BEGIN
DROP TABLE #RADHE
END
CREATE TABLE #Radhe(
DB sysname not null,
parent_name nvarchar(600) not null,
object_id int not null,
trigger_name sysname not null,
is_disabled bit,
i int not null identity(1,1),
[trigger_definition] NVARCHAR(MAX) not null
);
DECLARE @object_id int;
DECLARE @SQL nvarchar(max);
DECLARE @theSQL nvarchar(max);
declare @DB sysname
declare @parent_name nvarchar(600)
declare @trigger_name sysname
declare @is_disabled bit
select @DB = db_name(db_id())
--select @DB
SELECT @SQL =
'-------------------------------------------------------------------------------------------------------------------
DECLARE
@olddelim nvarchar(32) = char(13) + Char(10),
@newdelim nchar(1) = NCHAR(9999); -- pencil (✏)
SELECT the_db_name = quotename(db_name(db_id()))
,parent_name = @parent_name
,the_object_id = @obj_id
,trigger_name = @trigger_name
,is_disabled = @is_disabled
,trigger_definition = [Value]
FROM master.dbo.splitstring(OBJECT_DEFINITION(@obj_id), @olddelim);
-------------------------------------------------------------------------------------------------------------------';
-- PRINT @SQL
BEGIN TRY
DECLARE the_triggers CURSOR STATIC LOCAL FORWARD_ONLY READ_ONLY
FOR
SELECT
object_id=s.object_id
,parent_name = QUOTENAME(OBJECT_SCHEMA_NAME(s.object_id)) + '.' + QUOTENAME(OBJECT_NAME(s.object_id))
,trigger_name = QUOTENAME(s.name)
,s.is_disabled
FROM sys.triggers s
WHERE 1=1
OPEN the_triggers;
FETCH NEXT FROM the_triggers
INTO @object_id,@parent_name,@trigger_name,@is_disabled;
WHILE @@FETCH_STATUS = 0
BEGIN
SET @theSQL = 'EXEC ' + QUOTENAME(@DB) +
'.sys.sp_executesql @SQL' + CHAR(10) +
',N''@obj_id int,@parent_name nvarchar(600),@trigger_name sysname,@is_disabled bit'',' + CHAR(10) +
'''' + CAST (@object_id as nvarchar) + '''' + ',' +
'''' + @parent_name + '''' + ',' +
'''' + @trigger_name + '''' + ',' +
'''' + CAST (COALESCE(@is_disabled,0) as nvarchar) +
'''' +';' + CHAR(10)
-------------------------------------------
-- when @CHECK_IF_TRIGGER_EXISTS is on
-- add code that checks whether the trigger exists
-- and if it does drop it
-------------------------------------------
if @CHECK_IF_TRIGGER_EXISTS = 1
BEGIN
INSERT INTO #Radhe(DB,parent_name,object_id,trigger_name,is_disabled,trigger_definition) values
(QUOTENAME(db_name()),@parent_name,@object_id,@trigger_name,@is_disabled,'GO')
INSERT INTO #Radhe(DB,parent_name,object_id,trigger_name,is_disabled,trigger_definition) values
(QUOTENAME(db_name()),@parent_name,@object_id,@trigger_name,@is_disabled,'use ' + QUOTENAME(db_name()))
INSERT INTO #Radhe(DB,parent_name,object_id,trigger_name,is_disabled,trigger_definition) values
(QUOTENAME(db_name()),@parent_name,@object_id,@trigger_name,@is_disabled,'GO')
INSERT INTO #Radhe(DB,parent_name,object_id,trigger_name,is_disabled,trigger_definition) values
(QUOTENAME(db_name()),@parent_name,@object_id,@trigger_name,@is_disabled,'if OBJECT_ID('+ @trigger_name + ') is not null')
INSERT INTO #Radhe(DB,parent_name,object_id,trigger_name,is_disabled,trigger_definition) values
(QUOTENAME(db_name()),@parent_name,@object_id,@trigger_name,@is_disabled,' drop trigger '+ @trigger_name + ' ')
INSERT INTO #Radhe(DB,parent_name,object_id,trigger_name,is_disabled,trigger_definition) values
(QUOTENAME(db_name()),@parent_name,@object_id,@trigger_name,@is_disabled,'GO')
END
-------------------------------------------
-- do the insert here
-- the trigger source code
-------------------------------------------
--print @theSQL
INSERT INTO #Radhe(DB,parent_name,object_id,trigger_name,is_disabled,trigger_definition)
EXEC sys.sp_executesql @theSQL
, N'@SQL nvarchar(max) '
, @SQL = @sql
-- add a GO after the trigger definition
INSERT INTO #Radhe(DB,parent_name,object_id,trigger_name,is_disabled,trigger_definition) values
(QUOTENAME(db_name()),@parent_name,@object_id,@trigger_name,@is_disabled,'GO')
-- fetch the next trigger
FETCH NEXT FROM the_triggers
INTO @object_id,@parent_name,@trigger_name,@is_disabled;
END
-------------------------------------------
BEGIN TRY
--clean it up
CLOSE the_triggers;
DEALLOCATE the_triggers;
END TRY
BEGIN CATCH
--do nothing
END CATCH
-------------------------------------------
END TRY
BEGIN CATCH
-------------------------------------------
BEGIN TRY
--clean it up
CLOSE the_triggers;
DEALLOCATE the_triggers;
END TRY
BEGIN CATCH
--do nothing
END CATCH
-------------------------------------------
DECLARE @ERRORMESSAGE NVARCHAR(512),
@ERRORSEVERITY INT,
@ERRORNUMBER INT,
@ERRORSTATE INT,
@ERRORPROCEDURE SYSNAME,
@ERRORLINE INT,
@XASTATE INT
SELECT
@ERRORMESSAGE = ERROR_MESSAGE(),
@ERRORSEVERITY = ERROR_SEVERITY(),
@ERRORNUMBER = ERROR_NUMBER(),
@ERRORSTATE = ERROR_STATE(),
@ERRORPROCEDURE = ERROR_PROCEDURE(),
@ERRORLINE = ERROR_LINE()
SET @ERRORMESSAGE =
(
SELECT CHAR(13) +
'Message:' + SPACE(1) + @ErrorMessage + SPACE(2) + CHAR(13) +
'Error:' + SPACE(1) + CONVERT(NVARCHAR(50),@ErrorNumber) + SPACE(1) + CHAR(13) +
'Severity:' + SPACE(1) + CONVERT(NVARCHAR(50),@ErrorSeverity) + SPACE(1) + CHAR(13) +
'State:' + SPACE(1) + CONVERT(NVARCHAR(50),@ErrorState) + SPACE(1) + CHAR(13) +
'Routine_Name:' + SPACE(1) + COALESCE(@ErrorProcedure,'') + SPACE(1) + CHAR(13) +
'Line:' + SPACE(1) + CONVERT(NVARCHAR(50),@ErrorLine) + SPACE(1) + CHAR(13) +
'Executed As:' + SPACE(1) + SYSTEM_USER + SPACE(1) + CHAR(13) +
'Database:' + SPACE(1) + DB_NAME() + SPACE(1) + CHAR(13) +
'OSTime:' + SPACE(1) + CONVERT(NVARCHAR(25),CURRENT_TIMESTAMP,121) + CHAR(13)
)
--We can also save the error details to a table for later reference here.
RAISERROR (@ERRORMESSAGE,16,1)
END CATCH
SELECT * FROM #RADHE