Go to DotNetFunda.com
  Welcome, Guest!  
LoginLogin  
{ Submit resources and get monthly gifts !!! }
Submit: Article | Interview Question | Tips | Joke | Question | Link || Search  
 Skip Navigation Links Home > Articles > Query foreign keys of all tables in a given database in SQL Server 2005

All Articles | Post Articles |  Subscribe to RSS

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: 1063 | Category: Sql Server | Level: Advance | Print Article |
ASP.NET 3.5 Hosting and MS SQL 2008!
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:






 Bookmark and Share kick it on DotNetKicks.com

Experience:4 year(s)
Home page:
Member since:Monday, October 29, 2007
Biography:
 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


Question: Why to use www.dotnetfunda.com google search?
Answer: This search has been especially optimized to search technical articles. You may find to-the-point results in comparison with other search.
Google
About Us | Contact Us | Privacy Policy and 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.
This site is best viewed with a resolution of 1280x720 (or higher) and Microsoft Internet Explorer 6.0+ or Firefox 2.0+.