MSSQL - syscomments

Sometimes in SQL I am looking for a procedure that references a certain text string or table or column.

The best script that I have come up when looking for procedural string references is a simple select against the syscomments and sysobjects tables.

Syntax
Select a.name from sysobjects a
join syscomments b
on
a.id = b.id
and a.xtype = 'P' 
where b.text like '%mytextstring%'

Examlple
Select a.name from sysobjects a
join syscomments b
on
a.id = b.id
and a.xtype = 'P'
where b.text  like '%xp_sendmail%'

The example will look for any procedure within the current database that has the text string of xp_sendmail in it.  Once it returns the object names then I can review the procedural code for those procedures that returned.

I find this very useful when I am trying to find procedures that use "xp_sendmail" or other items like referencing a certain value or hard coded items from old code.  I also find this very useful when I need to write a new procedure for some reason or another and there may already be a similar procedure that I can copy part of the code from.  Especially if you are new to an organization this is very useful as a lot of times you don't always know what all of the procedures do, this is a handy way to look for specific key words and values to see if a procedure is similar in nature to what you are trying to accomplish.