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

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!