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