How do I find a stored procedure containing <text>?

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

SELECT OBJECT_NAME(id)     FROM SYSCOMMENTS     WHERE [text] LIKE '%Foo%'     AND OBJECTPROPERTY(id, 'IsProcedure') = 1     GROUP BY OBJECT_NAME(id)

SELECT OBJECT_NAME(object_id)    FROM sys.sql_modules    WHERE OBJECTPROPERTY(object_id, 'IsProcedure') = 1    AND definition LIKE '%Foo%'

Grab yourself a copy of the free Red-Gate SQL Search tool and start enjoying searching in SQL Server! :-)

It's a great and very useful tool, and YES! it's totally, absolutely FREE for any kind of use.

I took Kashif's answer and union'd all of them together. Strangely, sometimes, I found results in one of the selects but not the other. So to be safe, I run all 3 when I'm looking for something. Hope this helps:

DECLARE @SearchText varchar(1000) = 'mytext';SELECT DISTINCT SPName FROM (    (SELECT ROUTINE_NAME SPName        FROM INFORMATION_SCHEMA.ROUTINES         WHERE ROUTINE_DEFINITION LIKE '%' + @SearchText + '%'         AND ROUTINE_TYPE='PROCEDURE')    UNION ALL    (SELECT OBJECT_NAME(id) SPName        FROM SYSCOMMENTS         WHERE [text] LIKE '%' + @SearchText + '%'         AND OBJECTPROPERTY(id, 'IsProcedure') = 1         GROUP BY OBJECT_NAME(id))    UNION ALL    (SELECT OBJECT_NAME(object_id) SPName        FROM sys.sql_modules        WHERE OBJECTPROPERTY(object_id, 'IsProcedure') = 1        AND definition LIKE '%' + @SearchText + '%')) AS TORDER BY T.SPName