Finding text or field in T-SQL Stored Procedures, Tables, Views, etc..

I highly recommend to install this addon developed by Red Gate to SQL Management Studio.  You can search any text in all database objects (stored procedures, tables, views, triggers, functions and others).  I find it especially useful to find a stored procedure instead of using the inbuilt Management Studio filtering function. You can then double click it, and you will be redirected to the stored procedure.  I also use it when I have to rename a field from a table and have to find which stored procedures are currently listing that field in order to rename it.

I use it all the time, it's a great time-saver and it's free!

http://www.red-gate.com/products/sql-development/sql-search/

Here's how it looks like:

If you don't have the time to install, I used this stored procedure which would list the objects which match text that i am looking for together with the number of times found

CREATE PROCEDURE [dbo].[spr_FindText]
 @text nvarchar(max)
AS

select object_name(id) AS [Object], count(*) AS [Count]
from syscomments
where text like '%' + @text + '%'
GROUP BY object_name(id)
ORDER BY Object