// do something here

As kind of a follow up to Search SQL Server database triggers for table name, I needed to search for some text that was used to throw an exception in my SQL Server database, but it has grown so large that I did not want to try to spelunk through each and every trigger, function, and stored procedure.

Luckily, in much the same way as the above post, you can query the sys.sql_modules table and find this in one shot. Here is how that looks, where you will of course fill in your text for “SEARCH TEXT HERE”:

SELECT 
    OBJECT_SCHEMA_NAME(module.object_id) AS SchemaName,
    OBJECT_NAME(module.object_id) AS ObjectName,
    object.type_desc AS ObjectType
FROM sys.sql_modules AS module
    JOIN sys.objects AS object ON module.object_id = object.object_id
WHERE module.definition LIKE '%SEARCH TEXT HERE%'
ORDER BY SchemaName, ObjectName

BTW, today is the sad 10th Anniversary of the passing of Hollywood icon Leonard Nimoy, stage and screen actor and director extraordinaire known mostly for his recurring roles in Mission: Impossible and Fringe. And there was some other TV show that he was on as well I think.

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.