I have often needed to find a stored procedure that contains a certain snippet of text, such as a text in a subject line. Below is a query I've used in the past but it has been pointed out to me that this has a fatal flaw: It only searches the first 4000 characters of the stored procedure:.

SELECT ROUTINE_NAME, ROUTINE_DEFINITION 
    FROM INFORMATION_SCHEMA.ROUTINES 
    WHERE ROUTINE_DEFINITION LIKE '%SEARCHSTRING%' 
    AND ROUTINE_TYPE='PROCEDURE'

An improved query is to use the following:

SELECT OBJECT_NAME(object_id), OBJECT_DEFINITION(object_id)
    FROM sys.procedures
    WHERE OBJECT_DEFINITION(object_id) LIKE '%SearchString%'

Thanks to biradar26@gmail.com for the heads up.