SQL Server provides system views to display meta data like Tables, Columns and Constraints. The following View or Stored Procedure brings together some of these system views to show your FK contraints.
Note the terminology – The referenced table and column as the “parent” in the relationship while the “child” is termed the Parent as it owns the constraint.
Although SQL Server will allow you to include more than one column in a relationship, you shouldn’t and this procedure is designed for relationship constraints based on a single column.
CREATE OR ALTER VIEW [dbo].[vwFK]
--CREATE OR ALTER PROCEDURE [dbo].[uspFK]
--Uncomment the above to make it a Stored Procedure
--An SP will allow you to use an Order By clause. See below
-- 20230505
AS
WITH
tblChild AS ( -- The Child table
SELECT
ChildTable_ID = object_id
, ChildTable_Name = name
FROM sys.tables
)
, colChild AS (
SELECT
ChildTable_ID = object_id
, ChildTable_Name
, ChildColumn_Name = name
, Ordinal_Position = column_id
FROM sys.columns
JOIN tblChild On sys.columns.object_id = tblChild.ChildTable_ID
)
, tblParent AS ( -- Actually the Referenced Table
SELECT
ParentTable_ID = object_id
, ParentTable_Name = name
FROM sys.tables
)
, colParent AS ( -- Actually the Referenced Column
SELECT
ParentTable_ID
, ParentTable_Name
, ParentColumn_Name = name
, ParentOrdinal_Position = column_id
FROM sys.columns
JOIN tblParent On sys.columns.object_id = tblParent.ParentTable_ID
)
, FK AS (
SELECT
FK_ID = object_id
, FK_Name = name
, DeleteAction_ID = delete_referential_action
, DeleteAction_Name = delete_referential_action_Desc
, UpdateAction_ID = update_referential_action
, UpdateAction_Name = update_referential_action_Desc
FROM sys.foreign_keys
)
SELECT
FK_ID
, ChildTable_ID
, ParentTable_ID
, FK_Name
, ChildTable_Name
, ChildColumn_Name
, Ordinal_Position
, ParentTable_Name
, ParentColumn_Name
, ParentOrdinal_Position
, DeleteAction_ID
, DeleteAction_Name
, UpdateAction_ID
, UpdateAction_Name
FROM sys.foreign_key_columns FKCol
JOIN FK
ON FKCol.constraint_object_id = FK.FK_ID
JOIN colChild
ON FKCol.parent_column_id = colChild.Ordinal_Position
AND FKCol.parent_object_id = colChild.ChildTable_ID
JOIN colParent
ON FKCol.referenced_object_id = colParent.ParentTable_ID
AND FKCol.referenced_column_id = colParent.ParentOrdinal_Position
--ORDER BY Table_Name, Column_Name -- Uncomment to use in SP