Query foreign keys of all tables in a given database in SQL Server 2005

Deeraj
Posted by in Sql Server category on for Advance level | Views : 8640 red flag

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:



Page copy protected against web site content infringement by Copyscape

About the Author

Deeraj
Full Name: Deeraj Chakravarthy
Member Level: Starter
Member Status: Member
Member Since: 10/29/2007 1:00:04 AM
Country: India


Qualification: BCA, MScIS, MDSE, PMI-ACP

Login to vote for this post.

Comments or Responses

Login to post response

Comment using Facebook(Author doesn't get notification)