Tip: Searching Stored Procedures Text
Here is another tip for those who need to explore big SQL databases with a lot of stored procedures and quite a bit of logic. This will gives you a list of all the procedures that has a certain string in them:
CREATE PROCEDURE dev_FindInSP
select distinct name from sys.procedures procs join syscomments comments
on procs.object_id = comments.id where [text] like '%' + @str + '%'