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…
Pingback: Analyzing SQL Server Dependencies – Unresolved Dependencies in sys.sql_expression_dependencies | devioblog
Pingback: Series: Analyzing SQL Server Dependencies | devioblog