Text search in stored proc SQL Server

Question

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?

Answer 1

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.

Answer 2

You can use as well:

select distinct object_name(id) from sys.syscomments where text like '%SearchTextHere%'

Answer 3

Updated: There are several equivalent ways. Here's one:

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