Text search in SQL Server stored procedure

Question

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

Answer 1

INFORMATION_SCHEMA.ROUTINES or syscomments are not reliable.

The text field is nvarchar (4000) for both (only for systems with multiple lines). Thus, your search text may be lost at the border for systems or never found for INFORMATION_SCHEMA.ROUTINES

sys.sql_modules .definition - nvarchar (max)

 SELECT OBJECT_NAME(object_id) FROM sys.sql_modules WHERE definition LIKE '%mytext%' 

Edit, October 2011

Updating this answer.

Red Gate SQL Search is a free SSMS plugin that is very useful.

Answer 2

Updated: There are several equivalent ways. Here is one:

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

Answer 3

You can also use:

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

Answer 4

Do you want to search for text through stored procedures themselves?

Or table data?

If the table data, what about LIKE ?