Hierarchical List of All tables

In a SQL Server DB, I have to find all the "Master"(Parent) tables and also build a Hierarchical list of Paerent/Child tables. Finally I would like to traverse that hierarchical list from down and delete all the child table data at the end i can able to delete开发者_运维百科 the parent data also.

I have tried in one way, that is, Using system tables (like sys.objects etc) I queried the metadata of the db (like its primary and Foreign keys). But I don't know how to formulate the tree like structure.

try this in SQL Server Management Studio:

EXEC sp_msdependencies @intrans = 1 

if you insert the results into a temp table, you could then filter it to be just tables, just views, or use the other, alternative parameters for the proc to do the same thing

EXEC sp_msdependencies @intrans = 1 ,@objtype=8  --8 = tables
EXEC sp_msdependencies @intrans = 1 ,@objtype=3  --3 = tables is the correct one

Check this for more Heirarchical