Analyzing SQL Server Dependencies – sys.sql_expression_dependencies

After I noticed that there are differences between the contents of sys.sql_dependencies and the View Dependencies dialog, I analyzed the statements and queries that View Dependencies uses to retrieve the dependencies, to find that the dialog also queries the system view sys.sql_expression_dependencies.

Great, I thought, and modified my original query

select type, name, *
from sys.objects o
where o.object_id not in 
    (select referenced_major_id from sys.sql_dependencies)
and o.type not in ('S', 'D', 'F', 'IT', 'PK', 'TR')
and o.name not like 'dev[_]%'
and o.is_ms_shipped = 0
order by o.type, o.name

to reference sys.sql_expression_dependencies instead of sys.dependencies:

select type, name, *
from sys.objects o
where o.object_id not in 
    (select referenced_id from sys.sql_expression_dependencies)
and o.type not in ('S', 'D', 'F', 'IT', 'PK', 'TR')
and o.name not like 'dev[_]%'
and o.is_ms_shipped = 0
order by o.type, o.name

only to retrieve an empty result set.

Huh?

It turns out that the referenced_id column of sys.sql_expression_dependencies may contain NULL values, and a

expression NOT IN (SELECT NULL)

is always false.

Changing the WHERE condition to

where o.object_id not in 
    (select referenced_id from sys.sql_expression_dependencies 
        where referenced_id is not null)

finally returned a useful result: objects that are (presumably) not referenced by any other SQL Server objects.

Almost…

Advertisements

2 Responses to Analyzing SQL Server Dependencies – sys.sql_expression_dependencies

  1. […] I noticed that the referenced_id column of sys.sql_expression_dependencies can be NULL, I wondered how these NULLed records could be […]

  2. […] Analyzing SQL Server Dependencies – 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: