Find Foreign Keys

,

This script is fairly self-explanatory. It uses the INFORMATION_SCHEMA views to return all foreign keys within a database including the Parent or Primary Key Table and Column(s) as well as the Child Table and Column(s). This could be adapted to work for any database by creating a stored procedure that accepts an @DbName parameter and adds it to the Views referenced in the From clause to create 3-part naming.

SELECT 
   RC.Constraint_Name AS FK_Constraint,
   RC.Constraint_Catalog AS FK_Database,
   RC.Constraint_Schema AS FK_Schema,
   CCU.Table_Name AS FK_Table,
   CCU.Column_Name AS FK_Column,
   RC.Unique_Constraint_Name AS PK_Constraint,
   RC.Unique_Constraint_Catalog AS PK_Database,
   RC.Unique_Constraint_Schema AS PK_Schema,
   CCU2.Table_Name AS PK_Table,
   CCU2.Column_Name AS PK_Column
FROM 
   information_schema.referential_constraints RC JOIN
   INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE CCU ON 
       RC.CONSTRAINT_NAME = CCU.CONSTRAINT_NAME JOIN
   INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE CCU2 ON
       RC.UNIQUE_CONSTRAINT_NAME = CCU2.CONSTRAINT_NAME
ORDER BY
   RC.Constraint_NAME  

Rate

5 (6)

Share

Share

Rate

5 (6)