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.

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Get hierarchy of related table

Author  Topic 

azharrahi
Starting Member

44 Posts

Posted - 2014-04-22 : 11:46:29
Hi
I have a database in which tables have relationships with other tables and make a n-level hierarchy such that

Parent 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 tables

PersonOrganisation -> Organisation -> Services -> ServiceAttendants

So 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 ServiceAttendants

I 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 Parent
LEFT JOIN sys.foreign_keys Child
On Parent.referenced_object_id = Child.parent_object_id
Where OBJECT_NAME(Child.referenced_object_id) = 'PersonOrganisation' OR
OBJECT_NAME(Child.parent_object_id) = 'PersonOrganisation'


Azhar Rahi
Software Engineer
Experts Desk Pvt Ltd,Lahore
Pakistan
   

- Advertisement -