If you want to search triggers for text in SQL Server 2005, here is how you can do it. You would execute this in the case that you are looking for a trigger that updates a certain table, but you cannot find it, and you do not want to go through all the tables one by one, here is what you can execute:
SELECT OBJECT_NAME(id) FROM syscomments WHERE [text] LIKE '%your_search_here%' AND OBJECTPROPERTY(id, 'IsTrigger') = 1 GROUP BY OBJECT_NAME(id)
And here is how you can search SQL server stored procedures (external link).



personlaly I prefer:
SELECT text
FROM syscomments
WHERE [text] LIKE ‘%trigger%’
– if you want a trg attached to specific table
AND [text] LIKE ‘%On Tablename%’
or use following statemant. it will produce same results, just fewer lines in T-SQL
SELECT * FROM sys.triggers WHERE name LIKE ‘%your_search_here%’
Would be more helpful if it list the tables too. Thanks