Earlier this week, I needed to identify all the stored procedures in my database which contained a particular string (in this case, a table name) so I could make a few updates. Luckily, since the database was on SQL Server, I just needed to query a couple of the system tables. For example:
FROM sys.sql_modules M
INNER JOIN sys.objects O ON M.object_id = o.object_id
WHERE M.definition LIKE '%what_your_looking_for%'