Generate Trigger DDL

---------------------------------------------------------------------------------------------------
--
-- File Name: Dynamically Output Trigger DDL to a file.sql
--
-- Description: Dynamically generates individual Trigger DDL SQL files to a specified folder.
--
-- Author: David B. Kranes - Microsoft SQL Server and Oracle Database Consultant
--
-- Date: 2013-11-18
--
---------------------------------------------------------------------------------------------------


SET NOCOUNT ON

DECLARE
@trName VARCHAR(50),
@trFileLoc VARCHAR(2000),
@fileCoptCmd_1 VARCHAR(2000),
@fileCoptCmd_2 VARCHAR(2000)


DECLARE trigSource_cursor CURSOR
FORWARD_ONLY READ_ONLY
FOR
SELECT o.name
FROM   sys.objects AS oINNER JOIN  sys.syscomments AS c ON o.object_id = c.id
WHERE   o.type = 'TR'

OPEN trigSource_cursor
FETCH NEXT 
FROM trigSource_cursor
INTO @trName

WHILE @@FETCH_STATUS = 0
BEGIN
SET @trFileLoc = '<your file location here>' + @trName + '.sql'
SET @fileCoptCmd_1 = 'bcp "select c.text FROM sys.objects AS o INNER JOIN sys.syscomments AS c ON o.object_id = c.id WHERE   o.type = ''TR'' AND o.name = ' + '''' + @trName + '''' +  '" queryout "'
SET @fileCoptCmd_2 = @fileCoptCmd_1 + @trFileLoc + '" -t -c -S<server -T -d<your database name here>'

EXEC master..xp_cmdshell @fileCoptCmd_2

-- Debug ONLY.  Comment out when not using.
--PRINT @fileCoptCmd_2

FETCH NEXT
FROM trigSource_cursor
INTO @trName
END

CLOSE trigSource_cursor
DEALLOCATE trigSource_cursor