Click here to Skip to main content
13,150,385 members (30,379 online)
Click here to Skip to main content
Add your own
alternative version

Stats

7.6K views
10 bookmarked
Posted 16 Sep 2016

List Primary Key and Foreign Key Relationship in Database - SQL Server

, 6 Oct 2016
Rate this:
Please Sign up or sign in to vote.
Display the list of all Tables, the Referenced columns, the Referencing Table, the Referencing columns and the Constraint name in a database

Introduction

Here's a quick SQL Server tip for displaying all the Primary key foreign key relationship from a database. For this table, I am using the AdventureWorks2012 database.

1. For all tables in a database, below is the query.

SELECT o2.name AS Referenced_Table_Name,
       c2.name AS Referenced_Column_As_FK,
       o1.name AS Referencing_Table_Name,
       c1.name AS Referencing_Column_Name,
s.name AS Constraint_name
FROM  sysforeignkeys fk
INNER JOIN sysobjects o1 ON fk.fkeyid = o1.id
INNER JOIN sysobjects o2 ON fk.rkeyid = o2.id
INNER JOIN syscolumns c1 ON c1.id = o1.id AND c1.colid = fk.fkey
INNER JOIN syscolumns c2 ON c2.id = o2.id AND c2.colid = fk.rkey
INNER JOIN sysobjects s ON fk.constid = s.id
ORDER BY o2.name

Here's the output:

2. For a specific table in a database, below is the query.

SELECT o2.name AS Referenced_Table_name,  
       c2.name AS Referenced_Column_Name,  
       o1.name AS Referencing_Table_name,  
       c1.name AS Referencing_column_Name,  
       s.name AS Constraint_name  
FROM sysforeignkeys fk  
INNER JOIN sysobjects o1 ON fk.fkeyid = o1.id  
INNER JOIN sysobjects o2 ON fk.rkeyid = o2.id  
INNER JOIN syscolumns c1 ON c1.id = o1.id AND c1.colid = fk.fkey  
INNER JOIN syscolumns c2 ON c2.id = o2.id AND c2.colid = fk.rkey  
INNER JOIN sysobjects s ON fk.constid = s.id  
WHERE o2.name='Product'  -- Replace the Table_Name with actual DB Table name  

Here's the output:

There's also a command:

exec sp_fkeys @pktable_name ='Product',
        @pktable_owner ='Production'

And here's the output:

Hope this helps.

Thanks for reading.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Hussain Patel
Technical Lead
United States United States
Having 11+ years of experience on Microsoft Technologies.
Extensively worked on both windows based and web based application development.
My focus has been more on Web technologies. Excellent working knowledge on C#, ASP.NET, ASP.NET MVC, JavaScript, Ajax, HTML, CSS, WCF,JQuery, WCF, Web Services. Have worked on SQL server Databases and Stored procedures.
Currently working on Kendo UI and Kendo Charts..
Have worked on WPF, XAML, Expression blend. I keep in updating my self with new technologies and try out new tools and demos in my free time.
I have knowledge in HTML5, JQuery UI, Entity Framework, Enterprise Library, Ms-Build, MS-fakes and Microsoft SharePoint and Microsoft CRM dynamics.

Recently I have started reading and working on IOT application, got a Raspberry Pi and Arduino. I have self-learned Python and scratch.

ASP.NET Article of the Day

1) Title : ASP-NET-MVC-application-with-Custom-Bootstrap-Theme : Oct 7, 2016

You may also be interested in...

Comments and Discussions

 
Question[My vote of 2] Wy do you use the old system views? Pin
Malte Klena12-Oct-16 0:24
memberMalte Klena12-Oct-16 0:24 
AnswerRe: [My vote of 2] Wy do you use the old system views? Pin
Hussain Patel12-Oct-16 6:31
professionalHussain Patel12-Oct-16 6:31 
PraiseNice Pin
SteveHolle16-Sep-16 5:47
memberSteveHolle16-Sep-16 5:47 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

Permalink | Advertise | Privacy | Terms of Use | Mobile
Web01 | 2.8.170924.2 | Last Updated 7 Oct 2016
Article Copyright 2016 by Hussain Patel
Everything else Copyright © CodeProject, 1999-2017
Layout: fixed | fluid