In order to get the text of a trigger, you can use (just like for stored procedure) the sp_helptext stored procedure:

sp_helptext mytriggername

But there are a few problems with this:

• It’s not easy to further use the output of the procedure in further processing. You need to work with the loopback adapter and create a proxy table…
• The text is stored in chunks of 255 characters so if you just execute the procedure and redirect to a file, you get unwanted newlines in there

The text of triggers and stored procedure is stored in the syscomments table:

1> sp_help syscomments
2> go
 Column_name Type     Length Prec Scale Nulls Default_name Rule_name Access_Rule_name Computed_Column_object Identity
 ----------- -------- ------ ---- ----- ----- ------------ --------- ---------------- ---------------------- ----------
 id          int           4 NULL  NULL     0 NULL         NULL      NULL             NULL                            0
 number      smallint      2 NULL  NULL     0 NULL         NULL      NULL             NULL                            0
 colid       smallint      2 NULL  NULL     0 NULL         NULL      NULL             NULL                            0
 texttype    smallint      2 NULL  NULL     0 NULL         NULL      NULL             NULL                            0
 language    smallint      2 NULL  NULL     0 NULL         NULL      NULL             NULL                            0
 text        varchar     255 NULL  NULL     1 NULL         NULL      NULL             NULL                            0
 colid2      smallint      2 NULL  NULL     0 NULL         NULL      NULL             NULL                            0
 status      smallint      2 NULL  NULL     1 NULL         NULL      NULL             NULL                            0
 partitionid int           4 NULL  NULL     1 NULL         NULL      NULL             NULL                            0

The id column is the object id of the trigger or stored procedure. This can be used to make a join with the sysobjects table to have access to the object name:

select c.text 
from syscomments c, sysobjects o 
where o.id=c.id and o.name='mytriggername' order by c.colid

With the statement above you still get chunks of 255 characters. Now you need to iterate through the results and store them in a variable:

declare trigger_text_cursor cursor
for select c.text 
from syscomments c, sysobjects o 
where o.id=c.id and o.name='mytriggername' order by c.colid
for read only
go

declare @triggertext varchar(16384)
declare @text varchar(255)

open trigger_text_cursor
fetch trigger_text_cursor into @text

while @@sqlstatus = 0
begin
	set @triggertext=coalesce(@triggertext, '')+@text 
	fetch trigger_text_cursor into @text
end

close trigger_text_cursor
deallocate cursor trigger_text_cursor
select @triggertext
go

You can then use @triggertext to perform any further processing you need.

coalesce is used so that if a value is null, it will use the empty string. You could also do the same thing using isnull.

Note that it is not possible to declare a TEXT variable. Instead you have to declare a large VARCHAR variable (as done above). The only drawback is that the maximal length of such a variable is 16384 characters. If you have triggers or stored procedures with a longer text, you’ll have to implement the loop in a script or program (instead of using a cursor in Transact-SQL).