Search SQL Server triggers, functions, and stored procedures for text
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.