Analyzing SQL Server Dependencies – sys.sql_dependencies

To find out which SQL Server object depends on other objects, we can query the system view sys.sql_dependencies:

select,, * 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,


order by o.type,


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 not like 'dev[_]%'
and o.is_ms_shipped = 0
order by o.type,

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?

4 thoughts on “Analyzing SQL Server Dependencies – sys.sql_dependencies

  1. Pingback: Analyzing SQL Server Dependencies – View Dependencies | devioblog

  2. Pingback: Analyzing SQL Server Dependencies – sys.sql_expression_dependencies | devioblog

  3. Pingback: Analyzing SQL Server Dependencies – Client-side Dependencies | devioblog

  4. Pingback: Series: Analyzing SQL Server Dependencies | devioblog

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.