Analyzing SQL Server Dependencies – Unresolved Dependencies in sys.sql_expression_dependencies

SQL Server provides two system views, sys.sql_dependencies and sys.sql_expression_dependencies, to retrieve information about object dependencies.

As I noticed that the referenced_id column of sys.sql_expression_dependencies can be NULL, I wondered how these NULLed records could be useful.

A simple query returns the NULLed records:

select o.name, d.referenced_entity_name, *
from sys.sql_expression_dependencies  d
inner join sys.objects o on d.referencing_id = o.object_id
where referenced_id is null

The column referenced_entity_name contains the name of the object that could not be resolved to a reference_id when the referencing object was created.

At first glance, there seem to be two cases:

• EXEC storedprocedure where the schema name of the SP is not given
• UPDATE (et al) with table alias (on temp table)

Mysteriously, tables and views referenced without a schema name are not in the result set.

The case of the EXEC statements can be cleaned up easily by adding the schema name of the stored procedure. For the DML statements using table aliases, it’s best to choose alias names that are not used as object names.