Many times I need to search stored procedure for certain text. I find following code helpful:
select name, charindex('text to search',object_definition(object_id)), object_definition(object_id) from sys.procedures where charindex('text to search',object_definition(object_id)) > 0