To find out which SQL Server object depends on other objects, we can query the system view sys.sql_dependencies:
select o.name, ref.name, * from sys.sql_dependencies d inner join sys.objects o on o.object_id = d.object_id inner join sys.objects ref on ref.object_id = d.referenced_major_id
and additionally sort by the referencing or by the referenced objects using
order by ref.type, ref.name
order by o.type, o.name
As objects are created during development, modified, replaced, or becoming out-dated, it may not be clear which objects are still in use and which are not, and the question arises whether some of the object not referenced by any other are accessed by a client application.
To find all objects that are not referenced by other database objects, we can use the 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
This statement selects all objects that are not referenced by sys.sql_dependencies, filters out the constraints, triggers, and primary keys, as well as the pre-installed objects and some well-known user-defined objects (beginning with “dev_”, in my case).
However the result also contained functions etc. that I knew where definitely referenced, as a right-click and View Dependencies confirmed, so there must be more information available. What does SSMS do that sql_dependencies alone does not achieve?