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.

Advertisements

2 Responses to Analyzing SQL Server Dependencies – Unresolved Dependencies in sys.sql_expression_dependencies

  1. […] cleaning up unresolved object references in an SQL Server database, we can query the database for objects not used inside the database using the […]

  2. […] Analyzing SQL Server Dependencies – Unresolved Dependencies in sys.sql_expression_dependencies […]

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

%d bloggers like this: