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… 😉
Pingback: Analyzing SQL Server Dependencies – sys.sql_expression_dependencies | devioblog
Pingback: Series: Analyzing SQL Server Dependencies | devioblog
You can try a free addin for SQL Server Management Studio – SQL Refactor Studio (http://sqlrefactorstudio.com/)
– 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!