Search Inside a SQL Server Stored Procedure Definition

I found myself in a strange situation recently. A couple of months ago, I had written a couple of Stored Procedure in SQL Server 2005/2008. A week ago, when the time came to use it, I could barely remember its name or any of the columns used in the SP. What I did remember was the name of a variable I had declared inside the SP. If you fall in a similar situation and want to search inside a SQL Server Stored Procedure, here’s how to do it.

I am using the AdventureWorks database as an example. I will search for all stored procedures that have @ManagerID declared as a parameter in their definition. Here’s the query:

SELECT obj.name AS SP_NAME,
    sqlmod.definition AS SP_DEFINITION
FROM sys.sql_modules AS sqlmod
INNER JOIN sys.objects AS obj
ON sqlmod.object_id = obj.object_id
WHERE sqlmod.definition LIKE '%@ManagerID%'
    and type = 'P'

OUTPUT