Go to DotNetFunda.com
 Welcome, Guest!  
LoginLogin  
Take a break and be productive! read technical jokes.
 Skip Navigation Links Home > Articles > Sql Server > Query foreign keys of all tables in a given database in SQL Server 2005

All Articles | Submit Article |

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

 Posted on: 10/29/2007 5:20:25 AM by Deeraj | Views: 1650 | Category: Sql Server | Level: Advance | Print Article
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.

 Get Career Counseling  
DotNetFunda.Com brings you a FREE Career Counseling section where you can ask any type of career related question. Ask now!

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:




Interesting?   Bookmark and Share


Experience:4 year(s)
Home page:
Member since:Monday, October 29, 2007
Biography:Qualification: BCA, MScIS, MDCA
 Latest post(s) from Deeraj

   ◘ Recursive FTP folder deletes in .NET 2.0 posted on 8/26/2008 2:16:16 PM
   ◘ Generating XML from relational database tables posted on 8/4/2008 8:25:14 AM
   ◘ ToolTip for List Items posted on 7/24/2008 4:16:14 AM
   ◘ Debugging Stored Procedures in SQL Server 2005 posted on 11/12/2007 6:56:44 AM
   ◘ Tracking Object (Table/Function/Stored Procedure etc.,) changes in SQL Server 2005 posted on 11/7/2007 5:53:44 AM


 Submit Article

About Us | Contact Us | Privacy Policy | Terms of Use | Link Exchange | Members | Go Top
All rights reserved to DotNetFunda.Com. Logos, company names used here if any are only for reference purposes and they may be respective owner's right or trademarks.
(Best viewed in IE 6.0+ or Firefox 2.0+ at 1024 * 768 or higher)