Let’s say you remember a piece of code you need to optimize or take a look at, but you don’t remember in which stored procedure you saw it.
The following script searches for a certain string inside the code of all programmatic objects in the current database (stored procedures, triggers, views, and user-defined functions), and returns the objects which contain it.
SELECT
SchemaName = SCHEMA_NAME ([Objects].schema_id) ,
ObjectName = [Objects].[name] ,
ObjectType = [Objects].[type_desc] ,
[Definition] = SQLModules.[definition]
FROM
sys.sql_modules AS SQLModules
INNER JOIN
sys.objects AS [Objects]
ON
SQLModules.[object_id] = [Objects].[object_id]
WHERE
SQLModules.[definition] LIKE N'%Your String Here%';
Here's a script in our Madeira Toolbox doing the same thing across all accessible databases in the instance:
https://github.com/MadeiraData/MadeiraToolbox/blob/master/Utility%20Scripts/Search%20String%20in%20All%20Server%20Databases.sql