Analyzing SQL Server Dependencies – View Dependencies

My “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 SSMS.

To find out which queries SSMS (2008) uses to detect dependencies, I opened SQL Server Profiler. To get all the queries issued, simply select the SQL:BatchStarting EventClass. I won’t reproduce the statements here, but the ones required start with

CREATE TABLE #tempdep...
INSERT INTO #tempdep...
declare @find_referencing_objects...

In the original INSERT INTO, the parameters @_msparam_0 and @_msparam_1 refer to the object name and the object schema name.

Copying the queries from Profiler to SSMS, and additionally declaring and assigning the 2 parameters returns a result set that contains all references for the requested object.

However, since View Dependencies displays the dependency hierarchy in a tree view, the result contains the complete try, not just the direct dependencies.

To filter only for the direct dependencies, and maintain the order as in View Dependencies, the last SELECT statement must be changed to end in

where t.relative_id = OBJECT_ID('myschema.myobject')
order by 3, 2

Partial success…😉

3 Responses to Analyzing SQL Server Dependencies – View Dependencies

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

  2. […] Analyzing SQL Server Dependencies – View Dependencies […]

  3. Evgeny says:

    You can try a free addin for SQL Server Management Studio – SQL Refactor Studio (

    Key Features:
    – Advanced Search dependences of object (databases, files on disk, query editor)
    – Advanced Search dependencies free text (in databases, files on disk, query editor)
    – Refactoring “Rename”
    – Refactoring “Add CRUD Methods”
    – Refactoring “Move columns”
    – Refactoring “Add lookup table”
    – Formatting SQL code
    – And many other useful features!

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: