Find all references to an object in an SQL Server

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

In SQL 2008 the DMV (Data Management Function) sys.dm_sql_referencing_entities was added. Its returns any object that references the object you pass it.

SELECT * FROM sys.dm_sql_referencing_entities('dbo.Table1', 'OBJECT')

Answer 2

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 3

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 4

Very late to the party, but...

You can use the system proc sys.sp_depends:

exec sys.sp_depends 'object_name'

The result is a table listing all of the database objects that depend on (i.e., reference) object_name. Each row contains the name and type of the referring object, along with other info columns, depending on the type of object_name.

Note: This proc was added in MS SQL Server 2008.

See: MSDN docs

The docs say that this proc may be removed in a future release, and to use sys.dm_sql_referencing_entities instead, but it's still alive and kicking in MS SQL 2017.