This is a simple and useful query that I use very frequently. Sometimes it might be interesting to know all the sprocs where a specific field or table is being used, so this query could help us with that.
SELECT name, Object_definition(object_id) FROM sys.procedures WHERE Object_definition(object_id) LIKE '%text%'
For this example, It will return all the sprocs containing the word “text”.
I also recommend the free tool from RedGate (SQL Search) that allows us to search on sprocs, tables, views, etc. from a nice and visual way.