3

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.

bummi
  • 27,123
  • 14
  • 62
  • 101
Vivekh
  • 4,141
  • 11
  • 57
  • 102

1 Answers1

1

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

Community
  • 1
  • 1
Developer
  • 8,390
  • 41
  • 129
  • 238