Let's says you need to find out all the stored procedures that reference a column or that just contains a text. You can use:

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

Things get simplier if you are in SQL Server 2005; start a new query and paste this:

SELECT Name 
    FROM sys.procedures 
    WHERE OBJECT_DEFINITION(object_id) LIKE '%foobar%' 

Reference: http://databases.aspfaq.com/database/how-do-i-find-a-stored-procedure-containing-text.html