How to get Parent Table, Reference Table, Foreign Key Constraint Name and Columns in SQL Server - SQL Server / TSQL Tutorial Part 71

Scenario:

You are working as SQL Server Developer, you are asked to provide the query that should return all the parent tables, reference tables, Foreign Key Constraints and Columns used in Foreign Key Constraint definition.

Solution:

We can use the system views to gather this information. In our below query we will be using three

system views
sys.foreign_keys
sys.foreign_key_columns
sys.columns 

to answer the request. As we can have composite primary key columns used in Foreign Key Constraint, I have used FOR XML Path to concatenate rows into column so can provide list of columns in single row.

;With CTE_FK AS (
SELECT Schema_Name(Schema_id) as TableSchemaName,
  object_name(FK.parent_object_id) ParentTableName,
  object_name(FK.referenced_object_id) ReferenceTableName,
  FK.name AS ForeignKeyConstraintName,c.name as ReferencedColumnList,
  cf.name as ParentColumnName 
       FROM sys.foreign_keys AS FK
       INNER JOIN sys.foreign_key_columns AS FKC
               ON FK.OBJECT_ID = FKC.constraint_object_id
               INNER JOIN sys.columns c
          on  c.OBJECT_ID = FKC.referenced_object_id
                 AND c.column_id = FKC.referenced_column_id
                 INNER JOIN sys.columns cf
          on  cf.OBJECT_ID = FKC.parent_object_id
                 AND cf.column_id = FKC.parent_column_id
                 )
                 Select TableSchemaName,
                 ParentTableName,
                 ReferenceTableName,
                 ForeignKeyConstraintName,stuff((
                 Select ','+ParentColumnName
                 from CTE_FK i
                 where i.ForeignKeyConstraintName=o.ForeignKeyConstraintName
                 and i.TableSchemaName=o.TableSchemaName
                 and i.ParentTableName=o.ParentTableName
                 and i.ReferenceTableName=o.ReferenceTableName
                 for xml path('')), 1, 1, '') ParentColumnList
                 ,stuff((
                 Select ','+ReferencedColumnList
                 from CTE_FK i
                 where i.ForeignKeyConstraintName=o.ForeignKeyConstraintName
                 and i.TableSchemaName=o.TableSchemaName
                 and i.ParentTableName=o.ParentTableName
                 and i.ReferenceTableName=o.ReferenceTableName
                 for xml path('')), 1, 1, '') RefColumnList
                 from CTE_FK o
                 group by 
                 tableSchemaName,
                 ParentTableName,
                 ReferenceTableName,
                 ForeignKeyConstraintName

I executed above script on one of my database and here is output with Schema Name,Parent Table Name, Referenced Table Name, Foreign Key Constraint Name, Parent Column List and Reference Column List used in Constraint.

How to get Parent Table, Referenced Table,Foreign Key Constraint Name, Columns list in SQL Server

Video Demo : How to get Primary Key Table,Foreign Key Table and Constraint Name in SQL Server