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 Responses to Analyzing SQL Server Dependencies – sys.sql_dependencies

  1. […] “exploration tour” through SQL Server dependencies started when I noticed that the sys.sql_dependencies view does not contain all the dependencies listed in the View Dependencies dialog that can be opened by right-clicking an object in […]

  2. […] 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 […]

  3. […] As stated, the object names are filtered according to my naming conventions, and your statements may look a bit different. […]

  4. […] Analyzing SQL Server Dependencies – sys.sql_dependencies […]

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 )

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: