Looping through triggers collection?

[Kalor]

I can't find any help on this anywhere!

I'm upgrading a big 3rd party app to SQL Server 2000, and I need to loop through every trigger on a database and look for a certain keyword in the trigger's script. Any ideas?

 

[rt63]

select a.name, b.colid, b.text
from sysobjects a , syscomments b
where a.id = b.id
and a.type = 'TR'
and b.text like '%<key word value>%'
order by a.name, b.colid


syscomments table stores the text of the procedures/triggers in multiple rows for the same object. In a row it stores a maximum of 4000 characters. So colid will indicate the corresponding section of the code. This will not work if the triggers have been encrypted. If you only need a list of triggers then you can use the following query instead.


select a.name
from sysobjects a
where a.type = 'TR'
and exists (select * from syscomments b
where a.id = b.id
and b.text like '%<key word value>%')


RT

 

[Kalor]

Thanks, I should have known that.
(I'd already found a work around- I scripted all triggers in Ent Mgr and searched the file ).