Text search in Stored Procedures in SQL Server 2005

Question

How do I find a stored procedure containing a certain text? While I understand that the best place to do this kind of searching is through your source control tool, but are there ways to do this in the database?

Answer 1

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

Answer 2

You can search sys.sql_modules. Definition contains the text of procedures. The view contains procedures, views, udfs etc. To restrict yourself to stored procedures you should join with sys.procedure on object_id.