Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
azharrahi
Starting Member
44 Posts |
 Posted - 2014-04-22 : 11:46:29
|
| HiI have a database in which tables have relationships with other tables and make a n-level hierarchy such thatParent Table -> Child tables -> Grand-child tables and so on.So this means that any child table of root parent table can be parent of some other tables. There can be different/multiple hierarchies of such relationships.Now I want a query on sys objects (e.g. sys.foreign_keys etc) to retrieve all child tables against a Parent table. e.g I have following related tablesPersonOrganisation -> Organisation -> Services -> ServiceAttendantsSo when if I need to get all child tables of PersonOrganisation then it should return hierarchy from Organisation to ServiceAttendants and if I want to get child tables of Organisation, it should return hierarchy from Services to ServiceAttendantsI have written a following Query. It seems fine but I just want to reconfirm or a better query approach than this. Select OBJECT_NAME(Parent.parent_object_id) ForeignKeyTable, OBJECT_NAME(Parent.referenced_object_id) PrimaryKeyTable, Parent.name From sys.foreign_keys ParentLEFT JOIN sys.foreign_keys Child On Parent.referenced_object_id = Child.parent_object_idWhere OBJECT_NAME(Child.referenced_object_id) = 'PersonOrganisation' OR OBJECT_NAME(Child.parent_object_id) = 'PersonOrganisation'Azhar RahiSoftware EngineerExperts Desk Pvt Ltd,Lahore Pakistan |
|
|
|
|
|
Get hierarchy of related table