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… 😉