A few months ago, I wrote a post about getting the text of a stored procedure or trigger. There was one limitation: It could only retrieve up to 16384 characters which is the limit for a varchar variable. Defining a text variable is not possible. Now I have a few triggers longer than 16384 and their text is being truncated.

So what are the options ?

1. Storing the text in a variable is not an option.
2. Creating a dummy table with one text row and appending the text is not option either since you’d then get an error saying you cannot add to a text column (you can only read it and store it, but not append to it).
3. Just selecting all corresponding rows in syscomments is not an option either, since it would split lines

I ended up have a table and storing one row per line in the trigger text. Identifying the lines basically means appending the text to a varchar(16384) variable, searching for a line feed, storing the text before the line feed in the table, doing this recursively until you got all lines currently in the buffer and resuming with the rest.

Here a stored procedure doing this:

create procedure print_trigger(@trigger_name varchar(255))
as
begin
	create table tempdb..triggertext (triggertext text)

	declare trigger_text_cursor cursor
	for select c.text 
	from pdir..syscomments c, pdir..sysobjects o 
	where o.id=c.id and o.name=@trigger_name order by c.colid
	for read only

	declare @triggertext varchar(16384)
	declare @triggertext2 varchar(16384)
	declare @text varchar(255)
	declare @index int

	open trigger_text_cursor
	fetch trigger_text_cursor into @text

	while @@sqlstatus = 0
	begin
		-- Appends each line of the trigger text in syscomments to the text already gathered.
		set @triggertext=coalesce(@triggertext, '')+@text 		
		-- Loop through the lines (delimited by a line feed
		select @index=charindex(Char(10),@triggertext)
		while (@index >0)
		begin
			select @triggertext2=substring(@triggertext,1,@index-1)			
			-- Add each line to the table
			if (@triggertext2 is not null)
				insert into tempdb..triggertext (triggertext) values(@triggertext2)
			-- Continue with the rest of the string
			select @triggertext=substring(@triggertext,@index+1,16384)
			select @index=charindex(Char(10),@triggertext)
		end
		fetch trigger_text_cursor into @text
	end

	close trigger_text_cursor
	deallocate cursor trigger_text_cursor

	if (@triggertext is not null)
		insert into tempdb..triggertext (triggertext) values(@triggertext)

	select triggertext from tempdb..triggertext
	drop table tempdb..triggertext
end
go

If you want to execute this with isql and write the output to a file, you additionally do the following:

• call isql with the -b option which disables the display of the table headers output.
• set nocount on to suppress X rows affected messages.
• set proc_return_status off to suppress the display of return statuses when stored procedures are called.
• pipe the result to the following sed command to remove all those annoying trailing spaces (basically removing all spaces before an end of line):

  sed 's/[[:space:]]*$//'