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






3.65/5 (9 votes)
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.