At times as a DBA/DB Programmer we may need to generate a report on foreign keys across all the tables in a given database. It would be a tedious job to use the SQL Server Management studio and build a relationship diagram. Here is a tip to quickly query the meta data and depict all the foreign keys.
select * from (
select
constid,
object_name(constid) as 'ConstraintName',
object_name(fkeyid) as 'Detail' ,
c.name as 'Detail.Column'
from
sys.sysforeignkeys f,
sys.columns c,
sys.tables t
where
f.fkey=c.column_id and
t.type_desc='USER_TABLE' and
c.object_id=f.fkeyid and
c.object_id=t.object_id
) as Detail,
(
select
constid,
object_name(rkeyid) as 'Master' ,
c.name as 'Master.Column'
from
sys.sysforeignkeys f,
sys.columns c,
sys.tables t
where f.rkey=c.column_id and
t.type_desc='USER_TABLE' and
c.object_id=f.rkeyid and
c.object_id=t.object_id
)as Master
where Detail.constid = Master.constid
Sample Output:
If you like this article, subscribe to our
RSS Feed. You can also
subscribe via email to our Interview Questions, Codes and Forums section.