SCRIPT TO LIST All TABLES AND FOREGIN KEY RELATIONSHIP WITH OTHER TABLES

List All Tables and Their Relationship with other tables via Foreign Key

Script:

WITH CTE AS (

    SELECT

        OBJECT_NAME(f.parent_object_id) AS ParentTable,

        COL_NAME(fc.parent_object_id, fc.parent_column_id) AS ParentColumn,

        OBJECT_NAME (f.referenced_object_id) AS ReferencedTable,

        COL_NAME(fc.referenced_object_id, fc.referenced_column_id) AS ReferencedColumn,

        f.name AS ForeignKey

    FROM 

        sys.foreign_keys AS f

    INNER JOIN 

        sys.foreign_key_columns AS fc ON f.object_id = fc.constraint_object_id

)

SELECT 

    t.name AS TableName,

    ISNULL(C.ParentTable, 'Standalone Table') AS ParentTable,

    ISNULL(C.ForeignKey, 'Standalone Table') AS ForeignKey

FROM 

    sys.tables AS t

LEFT JOIN 

    CTE AS C ON t.name = C.ReferencedTable

ORDER BY 

    CASE WHEN C.ReferencedTable IS NULL THEN 1 ELSE 0 END, 

    C.ReferencedTable, 

    t.name;

Output will look like below: