Find all the stored procedures with particular text..

Below query will help find all the stored procedures with a particular text. System table syscomments contains the text of stored procedure.

DECLARE @StringToSearch VARCHAR(500)

SET @StringToSearch = '%text in stored procedure%'

SELECT Distinct SO.Name

FROM sysobjects SO (NOLOCK)

INNER JOIN syscomments SC (NOLOCK) on SO.Id = SC.ID

AND SO.Type = 'P'

AND SC.Text LIKE @stringtosearch

ORDER BY SO.Name