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

Let us learn in this blog about 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.

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

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'

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

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'

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

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 posts I will write more in depth about other DMV, which also aids in finding referenced data.

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

, , ,
Previous Post
SQL SERVER – Fix Visual Studio Error : Connections to SQL Server files (.mdf) require SQL Server Express 2005 to function properly. Please verify the installation of the component or download from the URL
Next Post
SQL SERVER – Fix Error: Microsoft OLE DB Provider for SQL Server error ‘80040e07’ or Microsoft SQL Native Client error ‘80040e07’

Related Posts

15 Comments. Leave new

  • Nice post Pinal – great little tool!

    Reply
  • How about use 「sp_depends」?
    EXEC sp_depends @objname = N’Customer’;

    Reply
  • sp_depends objname works as well

    Reply
  • Girijesh Pandey
    December 3, 2012 11:24 pm

    nice one…

    Reply
  • Nice Pinal, but the output of second query is same as first one, which is wrong. I think it is wrongly pasted.

    Reply
  • sqlgobbledegook
    October 24, 2013 8:17 pm

    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.

    Reply
  • sys.dm_sql_referencing_entities() built-in founction is better

    Reply
  • 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!

    Reply
  • Hi All,
    i got an issue when i’m performing the snapshot replication for DR. when i tried to perform the snapshot replication i got the error as “Objects referenced by the Stored procedures are required”. I’m new to the SQL administration, can anyone help me on this issue to move forward.
    Thanks in advance.

    Reply
  • Nice post. Good work.

    Reply
  • Thanks, Pinal. Once again, you’re the best!

    Reply
  • J. Maurício V. L. Júnior
    July 12, 2016 8:25 pm

    Muito bom! (Very Good!)

    Reply
  • Thanks Pinal and Sorry for the late reply, but have you found a way of getting a column-level dependency?

    Reply
    • found below on StackExchange

      SELECT ReferencingObject = SCHEMA_NAME(o1.schema_id) + ‘.’ + o1.name,
      ReferencedObject = SCHEMA_NAME(o2.schema_id) + ‘.’
      + ed.referenced_entity_name,
      ColumnName = c.name,
      ReferencedObjectType = o2.type,
      ReferencingObjecType = o1.type
      FROM AdventureWorks2012.sys.sql_expression_dependencies ed
      INNER JOIN AdventureWorks2012.sys.objects o1
      ON ed.referencing_id = o1.object_id
      INNER JOIN AdventureWorks2012.sys.objects o2
      ON ed.referenced_id = o2.object_id
      INNER JOIN AdventureWorks2012.sys.sql_dependencies d
      ON ed.referencing_id = d.object_id
      AND d.referenced_major_id = ed.referenced_id
      INNER JOIN sys.columns c
      ON c.object_id = ed.referenced_id
      AND d.referenced_minor_id = c.column_id
      WHERE SCHEMA_NAME(o1.schema_id) + ‘.’ + o1.name = ‘HumanResources.vEmployee’
      ORDER BY ReferencedObject,
      c.column_id;

      Reply
      • Thanks Pinal, I actually found a similar example however for some reason the sys.sql_dependencies does not show the actual dependencies, looks like some dependencies are completely missing, I will keep trying

Leave a Reply

Menu