[SCRIPT] – How to find a specific String inside of all the Stored Procedure in a Database

Sometimes we found ourselves into specific situations where we need to find all the stored procedures or functions containing a specific string in their codes, like a table name, specific command etc…

For example, last week I found myself in a situation like that where we didn’t know the stored procedure responsible for populate a specific table. At that moment I’ve decided to use this handy little script, changing obviously the name of the table in the WHERE clause.

If you find yourself in a situation like that, this script will be really useful to you! You only need to change the indicated values accordingly and if you want to refine and improve your query, you only have to modify the WHERE clause based on your necessity, keeping in mind that you must write the string that you want to find always between two % mark.

USE [DATABASE_NAME]
GO

SELECT schema_name(o.schema_id) as [Schema], o.Name as [Name]
FROM sys.sql_modules sm
INNER JOIN sys.objects o
ON o.object_id = sm.object_id
WHERE sm.definition like  '%[INSERT_HERE_YOUR_STRING]%'
GO