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

Solution 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.

Solution 2:

You can use as well:

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

Solution 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 

Solution 4:

Are you wanting to search for text through the stored procedures themselves?

Or table data?

If table data, how about LIKE?