Q:

Text search in stored proc SQL Server

Does any one know the script to use for text search in SQL Server? I would like to search a text from all the stored proc inside the SQL Server, does anyone know what is the script I should use?

A:

INFORMATION_SCHEMA.ROUTINES or syscomments are not reliable.
The text field is nvarchar(4000) for both (over multiple rows syscomments only). So your search text can be lost on the boundary for a syscomments or never found for INFORMATION_SCHEMA.ROUTINES 
sys.sql_modules.definition is nvarchar(max)
SELECT
    OBJECT_NAME(object_id)
FROM
    sys.sql_modules
WHERE
    definition LIKE '%mytext%'

Edit, Oct 2011
Bringing this answer up to date.
Red Gate SQL Search is a free SSMS plug in that is quite useful.

