T-SQL Script to list foreign key relationships

In this article, we will see T-SQL scripts to list foreign key relationships between tables.

What is Referential dependency ?

Referential dependency is a constraint between two tables in a relational database that ensures data consistency. To establish a referential dependency, you define a foreign key on a table that refers to the primary key of another table.

The foreign key represents a column or a set of columns in the referencing table that must match the primary key in the referenced table. This ensures that the values in the foreign key column(s) always refer to an existing row in the referenced table.

Why Referential dependency is Needed ?

Referential dependency maintains data accuracy and consistency by preventing data from being inserted, updated, or deleted in a way that violates the relationship between tables. If the relationship between tables is violated, the database raises an error and rolls back the transaction that caused the error, ensuring that the data remains consistent.

Finding Referential dependency ?

To find referential dependency between tables in SQL Server, you can use the system views sys.foreign_keys and sys.foreign_key_columns.

The sys.foreign_keys view contains information about all foreign keys in the database, including their name, table name, referenced table name, and delete and update rules.

The sys.foreign_key_columns view contains information about the columns that participate in the foreign key relationship, including the column name, table name, and referenced column name and table name.

You can join these two views to get a list of all foreign key relationships in the database, along with the tables and columns involved.

T-SQL Script to list foreign key relationships

SELECT
    f.name AS foreign_key_name,
    OBJECT_NAME(f.parent_object_id) AS referencing_table_name,
    COL_NAME(fc.parent_object_id, fc.parent_column_id) AS referencing_column_name,
    OBJECT_NAME(f.referenced_object_id) AS referenced_table_name,
    COL_NAME(fc.referenced_object_id, fc.referenced_column_id) AS referenced_column_name
FROM 
    sys.foreign_keys AS f
INNER JOIN 
    sys.foreign_key_columns AS fc ON f.object_id = fc.constraint_object_id
ORDER BY 
    referencing_table_name, foreign_key_name

This script will return a result set with the name of the foreign key, the name of the referencing table, the name of the referencing column, the name of the referenced table, and the name of the referenced column for each foreign key relationship in the database. You can use this information to understand the referential dependencies between tables in your database.

T-SQL Script to get complete chain of referential dependencie

To get the complete chain of referential dependencies starting from a specific table, you can use a recursive Common Table Expression (CTE). Here’s an example script:

WITH CTE AS (
  -- Anchor member: find all tables that directly reference the starting table
  SELECT
    t.name AS referencing_table_name,
    f.name AS foreign_key_name,
    OBJECT_NAME(f.referenced_object_id) AS referenced_table_name
  FROM 
    sys.tables AS t
  INNER JOIN 
    sys.foreign_keys AS f ON t.object_id = f.parent_object_id
  WHERE 
    OBJECT_NAME(f.referenced_object_id) = '<starting_table_name>'
    
  UNION ALL
  
  -- Recursive member: find all tables that reference the tables from the previous iteration
  SELECT
    t.name AS referencing_table_name,
    f.name AS foreign_key_name,
    OBJECT_NAME(f.referenced_object_id) AS referenced_table_name
  FROM 
    CTE
  INNER JOIN 
    sys.tables AS t ON CTE.referenced_table_name = t.name
  INNER JOIN 
    sys.foreign_keys AS f ON t.object_id = f.parent_object_id
  WHERE 
    f.referenced_object_id NOT IN (SELECT object_id FROM sys.views)
)
-- Final select: show the complete chain of referential dependencies
SELECT 
  referencing_table_name,
  foreign_key_name,
  referenced_table_name
FROM 
  CTE
ORDER BY 
  referencing_table_name, foreign_key_name, referenced_table_name

In this script, replace <starting_table_name> with the name of the table for which you want to find the complete chain of referential dependencies.

The script first finds all tables that directly reference the starting table and adds them to the CTE as the anchor member. Then, in the recursive member, it finds all tables that reference the tables from the previous iteration, and adds them to the CTE as well. The recursion continues until no new tables are found.

Finally, the script selects the results from the CTE, which contains the complete chain of referential dependencies, and orders the results by the referencing table name, foreign key name, and referenced table name.

Note that this script may not work correctly if your database contains circular dependencies, where table A references table B, which references table C, which in turn references table A. In such cases, the script may enter an infinite recursion and fail to return any results.

Happy Learning ! Hope you find T-SQL Script to list foreign key relationships useful.