Using sys.sql_expression_dependencies as a Single Source to Find Referenced and Referencing Objects

By Lori Brown | Dependencies

Aug 23

I thought I would continue to expand on ways to view dependencies.  This post will give you an alternate way to find things referencing tables and finding things that are referenced by a stored procedure or view after my previous posts on the subject.  See these (https://www.sqlrx.com/finding_things_sql_that_reference_a_table/ & https://www.sqlrx.com/how-to-find-the-objects-and-columns-a-stored-procedure-or-view-uses/ )

A different way to find those same things can be done by using the sys.sql_expression_dependencies catalog view (https://docs.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-sql-expression-dependencies-transact-sql?view=sql-server-2017 ).

Finding Referencing Objects

For instance, you can use sys.dm_sql_referencing_entities to find what stored procedures or views that references the Sales.Orders table.

USE WideWorldImporters
GO
SELECT *
FROM sys.dm_sql_referencing_entities ('Sales.Orders', 'OBJECT');  
GO

Or you can use sys.sql_expression_dependencies to get a similar result.

SELECT OBJECT_SCHEMA_NAME ( referencing_id ) AS referencing_schema_name,  
    OBJECT_NAME(referencing_id) AS referencing_entity_name,   
    o.type_desc AS referencing_desciption,   
    COALESCE(COL_NAME(referencing_id, referencing_minor_id), '(n/a)') AS referencing_minor_id,   
    referencing_class_desc, referenced_class_desc,  
    referenced_server_name, referenced_database_name, referenced_schema_name,  
    referenced_entity_name,   
    COALESCE(COL_NAME(referenced_id, referenced_minor_id), '(n/a)') AS referenced_column_name,  
    is_caller_dependent, is_ambiguous  
FROM sys.sql_expression_dependencies AS sed  
INNER JOIN sys.objects AS o ON sed.referencing_id = o.object_id  
WHERE referenced_id = OBJECT_ID(N'Sales.Orders');  
GO  

Finding Referenced Objects

And conversely to find what tables are referenced by a stored procedure you can use sys.dm_sql_referenced_entities like so…

USE WideWorldImporters
GO
SELECT referenced_entity_name AS table_name, referenced_minor_name as column_name, is_selected, is_updated, is_select_all  
FROM sys.dm_sql_referenced_entities ('Website.InvoiceCustomerOrders', 'OBJECT');  -- stored procedure

Or you can again use sys.sql_expression_dependencies to get a similar result.

SELECT OBJECT_NAME(referencing_id) AS referencing_entity_name,   
    o.type_desc AS referencing_desciption,   
    COALESCE(COL_NAME(referencing_id, referencing_minor_id), '(n/a)') AS referencing_minor_id,   
    referencing_class_desc,  
    referenced_server_name, referenced_database_name, referenced_schema_name,  
    referenced_entity_name,   
    COALESCE(COL_NAME(referenced_id, referenced_minor_id), '(n/a)') AS referenced_column_name,  
    is_caller_dependent, is_ambiguous  
FROM sys.sql_expression_dependencies AS sed  
INNER JOIN sys.objects AS o ON sed.referencing_id = o.object_id  
WHERE referencing_id = OBJECT_ID(N'Website.InvoiceCustomerOrders');  
GO

In both cases you can see that sys.sql_expression_dependencies provides the same basic info.  But when it comes to tables are referenced by a stored procedure, sys.sql_expression_dependencies gives you less detail than you can get when using sys.dm_sql_referenced_entities.  The biggest difference in both examples is that with sys.sql_expression_dependencies you will get info on cross-database and cross-server dependencies which can be super helpful.

However, once again I have to sound a note of caution because even sys.sql_expression_dependencies does not catch things referenced in a dynamic SQL string.  Sales.Orders is found in dynamic SQL in the, [Application].[Configuration_EnableInMemory] & [DataLoadSimulation].[Configuration_ApplyDataLoadSimulationProcedures] stored procedures but it does not catch this.  So far the best way to find objects in dynamic SQL strings that I know of is to check for the object in sys.sql_modules.

USE WideWorldImporters
GO
SELECT * 
FROM sys.sql_modules
WHERE definition like '%Sales.Orders%'
GO

This should give you some good options to find dependencies and you also know of some of the limitations.

In my examples I have used the Wide World Importers database.  This sample database replaces the old AdventureWorks databases.  You can download the Wide World Importers OLTP and Data Warehouse databases here:

https://github.com/Microsoft/sql-server-samples/releases/tag/wide-world-importers-v1.0

Find out more about them here: https://docs.microsoft.com/en-us/sql/samples/wide-world-importers-what-is?view=sql-server-2017

For more information about blog posts, concepts and definitions, further explanations, or questions you may have…please contact us at SQLRx@sqlrx.com.  We will be happy to help!  Leave a comment and feel free to track back to us.  We love to talk tech with anyone in our SQL family!

Follow

About the Author

Lori is an avid runner, cyclist and SQL enthusiast. She has been working for SQLRX for 10 years and has been working with SQL in general for 20 years. Yup...she is an old hand at this stuff.

  • […] Lori Brown walks us through the sys.dm_sql_referencing_entities and referenced entities DMVs: […]

  • >