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

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.