We are currently making some changes to the database and needed a way of finding specific text within all the stored proceedures.
The following SQL achieved the task perfectly.
SELECT DISTINCT o.name AS Object_Name, o.type_desc, m.definition
FROM sys.sql_modules AS m
INNER JOIN sys.objects AS o
ON m.object_id=o.object_id
WHERE m.definition Like '%TEXT_HERE%'