SQL SERVER – Find Referenced or Referencing Object in SQL Server using sys.sql_expression_dependencies

A very common question which I often receive are:

How do I find all the tables used in a particular stored procedure?

How do I know which stored procedures are using a particular table?

Both are valid question but before we see the answer of this question – let us understand two small concepts – Referenced and Referencing.

Here is the sample stored procedure.

CREATE PROCEDURE mySP
AS
SELECT
*
FROM Sales.Customer
GO

Reference: The table Sales.Customer is the reference object as it is being referenced in the stored procedure mySP.

Referencing: The stored procedure mySP is the referencing object as it is referencing Sales.Customer table.

http://www.pinaldave.com/bimg/references.jpg

Now we know what is referencing and referenced object. Let us run following queries. I am using AdventureWorks2012 as a sample database. If you do not have SQL Server 2012 here is the way to get SQL Server 2012 AdventureWorks database.

Find Referecing Objects of a particular object

Here we are finding all the objects which are using table Customer in their object definitions (regardless of the schema).

USE AdventureWorks
GO
SELECT
referencing_schema_name = SCHEMA_NAME(o.SCHEMA_ID),
referencing_object_name = o.name,
referencing_object_type_desc = o.type_desc,
referenced_schema_name,
referenced_object_name = referenced_entity_name,
referenced_object_type_desc = o1.type_desc,
referenced_server_name, referenced_database_name
--,sed.* -- Uncomment for all the columns
FROM
sys.sql_expression_dependencies sed
INNER JOIN
sys.objects o ON sed.referencing_id = o.[object_id]
LEFT OUTER JOIN
sys.objects o1 ON sed.referenced_id = o1.[object_id]
WHERE
referenced_entity_name = 'Customer'

http://www.pinaldave.com/bimg/references1.jpg

The above query will return all the objects which are referencing the table Customer.

Find Referenced Objects of a particular object

Here we are finding all the objects which are used in the view table vIndividualCustomer.

USE AdventureWorks
GO
SELECT
referencing_schema_name = SCHEMA_NAME(o.SCHEMA_ID),
referencing_object_name = o.name,
referencing_object_type_desc = o.type_desc,
referenced_schema_name,
referenced_object_name = referenced_entity_name,
referenced_object_type_desc = o1.type_desc,
referenced_server_name, referenced_database_name
--,sed.* -- Uncomment for all the columns
FROM
sys.sql_expression_dependencies sed
INNER JOIN
sys.objects o ON sed.referencing_id = o.[object_id]
LEFT OUTER JOIN
sys.objects o1 ON sed.referenced_id = o1.[object_id]
WHERE
o.name = 'vIndividualCustomer'

http://www.pinaldave.com/bimg/references2.jpg

The above query will return all the objects which are referencing the table Customer.

I am just glad to write above query. There are more to write to this subject. In future blog post I will write more in depth about other DMV which also aids in finding referenced data.

Reference: Pinal Dave (http://blog.sqlauthority.com)

9 thoughts on “SQL SERVER – Find Referenced or Referencing Object in SQL Server using sys.sql_expression_dependencies

  1. Pingback: SQL SERVER – Weekly Series – Memory Lane – #007 « SQL Server Journey with SQL Authority

  2. I just came across this – neither the script or sp_depends work when the referenced object does not exist when the referencing object is created. After the referenced object is created the SP would need to be altered or recreated to register the dependency. If you were auditing a server you could miss a lot of dependencies due to this. In addition this would not track 3 part named objects in other db’s even if objects were created in the correct order. I prefer a more comprehensive check using sys.sql_modules, depending on the confidence level of the database sanitisation.

    Like

  3. First, thanks for this – very helpful! Almost exactly what I needed…

    “Almost”, because I have a few procedures that build a string and then exec it.
    (e.g., set @Query = “SELECT … FROM Object_I_Need_To_Find_References…”; exec @Query)

    Any tips on finding all of those – short of remembering where I put them (always dangerous)?

    Thanks!

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s