Relatively basic stuff here, but I do like to stick to the KISS rule. I am often required to search SQL Server's stored procedures to find out which ones refer to specific fields, tables, or even variable names. Recently, I had to refactor several stored procedures defined a specific alias for a column. Being the geek that I am, I wrote a little stored proc helper routine that quickly came up with the results I needed. In my case, there were over 1000 procs to search and my results turned up 25 procs to refactor.
CREATE PROCEDURE sp_find_procs_containing
@search VARCHAR(100) = ''
SET @search = '%' + @search + '%'
ROUTINE_DEFINITION LIKE @search
If you invoke this proc and pass it the phrase you are looking for in the proc, it will return them in sorted order. Not brain surgery, just something to help me code faster.
As you can tell, I like the INFORMATION_SCHEMA views. Here is another post I put up a while back on using them.