Find Parent of a Table

[Behseini]

Hi Guys
Could you please let me know what SQL query I can run to get the parent of a specified table?
I already used this code which is providing a list of child tables

SELECT object_name(parent_object_id)As 'Childs List'
FROM sys.foreign_keys
WHERE object_name(referenced_object_id) ='Stores'

How can I run a code like this to list parent of "Stores" table?
Thanks

[BitBlt]

Assuming you're using SQL2005 or SQL2008, this should do the trick:

select a.name as ParentName, c.name as ChildName
from sys.tables a
inner join sys.foreign_keys b
on a.object_id = b.referenced_object_id
inner join sys.tables c 
on b.parent_object_id = c.object_id

If you're using SQL2000 (yes, people still do that) you'll have to do something a little different:

select a.name as ParentName, c.name as ChildName
from sysobjects a
inner join sysforeignkeys b
on a.id = b.rkeyid
inner join sysobjects c 
on b.fkeyid = c.id
where a.type = 'U'

Hope this helps!

[Behseini]

Hi BitBlt
Thanks for your comment but as I said I do not want to get a list of relation between all tables. What I need is simply identifying parent of A Specified Table like 'Stores' in pub.
Thanks any way

[BitBlt]

Just add to the "Where" clause and specify your table name as needed.

Or, you could just turn the referenced parent/child around like so:

SELECT object_name(referenced_object_id)As 'Parent'
FROM sys.foreign_keys
WHERE object_name(parent_object_id) ='Stores'

The trick here is that if you look at the sys.foreign_keys view definition, the column "referenced_object_id" points to the parent end of the relationship. The column "parent_object_id" points to the owner of the relationship (the "child" end).

[Behseini]

Thanks man
this is exactly what I was looking for