Search SQL Server database triggers for table name
Did you ever find yourself in a situation where you have a SQL Server database that has grown out of control, and you need to figure out where a call to insert a record into a particular table might be lurking in one of those triggers?
Oh sure, you can go into each table, expand the triggers folder in each table, then crack open each trigger showing and see if what you are looking for is there. For a database of non-trivial size, I would rather look up “tedium” in the dictionary.
Alas, there is a way to quickly screen down the number of places you may have to look for to find what you need. Open up your database and paste this text into the query window, of course replacing SearchText with whatever table name you need to search for:
SELECT so.name, text FROM sysobjects so, syscomments sc WHERE TYPE = 'TR' AND so.id = sc.id AND text LIKE '%SearchText%' |
For example, if you wanted to find any trigger that inserts a record into a table called Customers, you would do this query:
SELECT so.name, text FROM sysobjects so, syscomments sc WHERE TYPE = 'TR' AND so.id = sc.id AND text LIKE '%insert into Customers%' |
Keep in mind that unless you played with the settings or collation of your database, the case of the text in the LIKE statement above should not matter.