Search for text in Stored Procedures

Very handy when you are asked to create a trace for a stored procedures that update text in a specific table. Then you want a way to search fast within all sp’s in your database.

SELECT distinct sysobjects.name
FROM sysobjects (nolock)
INNER JOIN syscomments (NOLOCK) on sysobjects.id = syscomments.id
AND sysobjects.type = 'P'
AND syscomments.text LIKE '%<text to search for>%'
ORDER BY sysobjects.name

Just replace <text to search for> with your search string and you are good to go!
Note that % around your word makes the query able to match more than you bargained for.

This entry was posted in DBA, SQL Server. Bookmark the permalink.

Leave a Reply

Your email address will not be published. Required fields are marked *