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).
2 thoughts on “Sybase: get the text of a trigger or stored procedure”