Find a Stored Procedure By Searching Its Contents

Today I had to do something for the first time in a while and it took me a moment to remember the syntax so I figured I'd better write a post about it so that doesn't happen again. In SQL Server you can search the contents of a stored procedure. This is useful when you're trying to figure out which stored procedure(s) update(s) a particular field, for example. I'm sure there are other uses, but I want to keep this as short as possible.

Basically what you can do is use the built-in sys tables to search the text (contents) of a stored procedure. It's pretty straightforward so I'll just get right to the code. This simple SQL statement will get the names of any stored procedures that use the field "CurrentMarriageStatus".

SELECT DISTINCT so.[name]
FROM sysobjects so
INNER JOIN syscomments sc
    ON so.id = sc.id
WHERE sc.[text] LIKE '%CurrentMarriageStatus%'

That's it! Then you can take the results and go through them one at a time to see how they're using the field you searched for.