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 ?
- Storing the text in a variable is not an option.
- 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).
- 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):