Here’s a cool little script my boss showed me. If you want to find a snippet of code in a stored procedure, but you can’t remember the stored procedures name, use this query:
select so.name as 'storProc' from sysobjects so join syscomments sc on so.id=sc.id where so.type='P' and sc.[text] like '%BIT_YOU_WANT_TO_FIND%'
Change the BIT_YOU_WANT_TO_FIND to whatever you want to search for.
Works a treat!