[SQL] How can I programmatically generate the table dependency hierarchy from selecting "View...

In SSMS, I can right-click on a view, SP or table in Object Explorer, select "View Dependencies", click on the circle next to "Objects on which [selected item] depends", and it produces a dependency hierarchy showing all the dependency objects.

I read this Microsoft article, which explained this process, and it also gave the following query for viewing dependencies.

SELECT *
FROM sys.sql_expression_dependencies
WHERE referencing_id = OBJECT_ID(N'<insert view/SP/table name here>')


However, thus far this has not returned output when I look for the dependencies of a table.

Is there a query that can reproduce the hierarchical output from selecting "View Dependencies" in the Object Explorer UI? The hierarchical representation itself is not very important to us, and a flattened hierarchy represented in table format would more than suffice for our purposes.

Thank you for your help.

Continue reading...