// 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.