script all triggers in a database I have a few databases involved in replication as subscribers and they have triggers and are used by a different set of applications.

Every time I need to reinitialise those database I need to script the triggers, permissions and indexes, apply the snapshot and then re-apply those triggers, indexes and permissions.

Is there a script that I can use to script all the triggers of a current database?
This seems a whole lot simpler than what you have:

CREATE TABLE #tmp
(
  db          sysname,
  sch         sysname,
  obj         sysname,
  name        sysname,
  is_disabled bit,
  def         nvarchar(max)
);
GO

INSERT #tmp SELECT DB_NAME(), 
  s.name, o.name, t.name, 
  t.is_disabled, m.definition
FROM sys.triggers AS t
INNER JOIN sys.sql_modules AS m
ON t.object_id = m.object_id
INNER JOIN sys.objects AS o
ON t.parent_id = o.object_id
INNER JOIN sys.schemas AS s
ON o.schema_id = s.schema_id
WHERE parent_class = 1;


When you're ready to create them again:

DECLARE @sql nvarchar(max) = N'';

SELECT @sql += def 
  + CHAR(13) + CHAR(10) + N'GO' 
  + CHAR(13) + CHAR(10) 
FROM #tmp;

SELECT @sql += N'DISABLE TRIGGER ' 
  + QUOTENAME(sch) + N'.' + QUOTENAME(name)
  + N' ON ' 
  + QUOTENAME(sch) + N'.' + QUOTENAME(obj) + N';'
FROM #tmp WHERE is_disabled = 1;

PRINT @sql;
-- EXEC sys.sp_executesql @sql;


Yes, you won't be able to validate the entire script is there, because SSMS won't output the entire value of @sql. See this post for workarounds.

Also, I don't know why you need the database as part of the dynamic script if you always know the database you're affecting, but there's a much safer way to make the database name dynamic without concatenating values and inviting SQL injection:

DECLARE @db sysname = N'AdventureWorks';

DECLARE @exec nvarchar(max), @sql nvarchar(max);

SET @exec = QUOTENAME(@db) + N'.sys.sp_executesql';

SET @sql = N'SELECT DB_NAME();';

EXEC @exec @sql;