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.