Find all references to an object in an SQL Server database

Question

I'm trying to find all references to an object in an SQL Server database.

How can I quickly search? SQL Server Management Studio does not seem to do it. I use http://www.red-gate.com/products/SQL_Search/ but I'd like to find the "official" Microsoft solution to this. Is it in another product?

For example, when I do a mass search in visual studio, I would like to be able to also find something in all stored procedures.

Or maybe I'm not coding this the right way?

Carl

Answer 1

Use:

select object_name(m.object_id), m.*   from sys.sql_modules m  where m.definition like N'%name_of_object%' 

...because SYSCOMMENTS and INFORMATION_SCHEMA.routines have nvarchar(4000) columns. So if "name_of_object" is used at position 3998, it won't be found. SYSCOMMENTS does have multiple lines, but INFORMATION_SCHEMA.routines truncates.