Microsoft SQL Server forum

Author

Related tables

newtophp2000@yahoo.com

2005-07-01, 11:23 am

Is it possible to retrieve all tables that a given one is related to
via foreign keys?

Razvan Socol

2005-07-02, 7:23 am

Try this:

SELECT DISTINCT o1.name FROM sysreferences r
INNER JOIN sysobjects o1 ON o1.id=r.fkeyid
INNER JOIN sysobjects o2 ON o2.id=r.rkeyid
WHERE o2.name='YourTable'
/*
UNION
SELECT DISTINCT o2.name FROM sysreferences r
INNER JOIN sysobjects o1 ON o1.id=r.fkeyid
INNER JOIN sysobjects o2 ON o2.id=r.rkeyid
WHERE o1.name='YourTable'
*/

Razvan

newtophp2000@yahoo.com

2005-07-06, 3:23 am

Razvan Socol wrote:
> Try this:
>
> SELECT DISTINCT o1.name FROM sysreferences r
> INNER JOIN sysobjects o1 ON o1.id=r.fkeyid
> INNER JOIN sysobjects o2 ON o2.id=r.rkeyid
> WHERE o2.name='YourTable'
> /*
> UNION
> SELECT DISTINCT o2.name FROM sysreferences r
> INNER JOIN sysobjects o1 ON o1.id=r.fkeyid
> INNER JOIN sysobjects o2 ON o2.id=r.rkeyid
> WHERE o1.name='YourTable'
> */

Thanks, this gets me closer to the goal. By the way, if I try it as it
is, it returns no rows at first. When I remove the WHERE clause, then
it works!

I will need to look more into the system tables.

Sponsored Links