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

I'm not sure of 'official microsoft' way but I've used SqlDigger in the past. It's not bad.

If you want to do it in VS, then you will need the text of all your procs included in your project.

Answer 2

I use this query to look for all tables (or text) in the stored procedures:

SELECT DISTINCT o.name, o.xtype
FROM syscomments c
INNER JOIN sysobjects o ON c.id=o.id
WHERE c.TEXT LIKE '%tablename%'

Answer 3

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.

Answer 4

There is a way to do this in SQL 2000 with the syscomments table and still get accurate results, as long as the string searched for is not longer than about 2000 characters. If anyone wants to know this, please post a comment and I'll dig up the code for you.

Answer 5

SQL Server Management Studio has a View Dependencies feature when you right click on an object in the Object Explorer. Is this what you're looking for?