Foreign Key Diagram from SQL Server

Run one of the queries below against your database, copy the results, and paste them here to see how the tables relate. Everything runs in your browser: the results you paste aren’t sent anywhere.

1. Run a query

Just the foreign keys. Returns one row per column in each foreign key. The diagram shows only the tables that have relationships, and only the columns those relationships use.

SELECT
    OBJECT_SCHEMA_NAME(fk.parent_object_id) AS ChildSchema,
    OBJECT_NAME(fk.parent_object_id) AS ChildTable,
    pc.name AS ChildColumn,
    OBJECT_SCHEMA_NAME(fk.referenced_object_id) AS ParentSchema,
    OBJECT_NAME(fk.referenced_object_id) AS ParentTable,
    rc.name AS ParentColumn,
    fk.name AS ForeignKeyName
FROM sys.foreign_keys fk
JOIN sys.foreign_key_columns fkc
    ON fk.object_id = fkc.constraint_object_id
JOIN sys.columns pc
    ON pc.object_id = fkc.parent_object_id
    AND pc.column_id = fkc.parent_column_id
JOIN sys.columns rc
    ON rc.object_id = fkc.referenced_object_id
    AND rc.column_id = fkc.referenced_column_id
ORDER BY ChildTable, ForeignKeyName, fkc.constraint_column_id;

Every column. Returns one row per column of every user table, with its type, whether it allows NULL, whether it’s in the primary key, and the foreign key it belongs to, if any. Tables without foreign keys are included too. A column that’s in more than one foreign key appears once for each.

SELECT
    s.name AS TableSchema,
    t.name AS TableName,
    c.name AS ColumnName,
    TYPE_NAME(c.user_type_id)
        + CASE
            WHEN TYPE_NAME(c.user_type_id) IN (N'varchar', N'char', N'varbinary', N'binary')
                THEN N'(' + IIF(c.max_length = -1, N'max', CAST(c.max_length AS nvarchar(10))) + N')'
            WHEN TYPE_NAME(c.user_type_id) IN (N'nvarchar', N'nchar')
                THEN N'(' + IIF(c.max_length = -1, N'max', CAST(c.max_length / 2 AS nvarchar(10))) + N')'
            WHEN TYPE_NAME(c.user_type_id) IN (N'decimal', N'numeric')
                THEN N'(' + CAST(c.precision AS nvarchar(10)) + N',' + CAST(c.scale AS nvarchar(10)) + N')'
            WHEN TYPE_NAME(c.user_type_id) IN (N'datetime2', N'datetimeoffset', N'time')
                THEN N'(' + CAST(c.scale AS nvarchar(10)) + N')'
            ELSE N''
          END AS DataType,
    c.is_nullable AS IsNullable,
    IIF(pkc.column_id IS NULL, 0, 1) AS IsPrimaryKey,
    OBJECT_SCHEMA_NAME(fkc.referenced_object_id) AS ParentSchema,
    OBJECT_NAME(fkc.referenced_object_id) AS ParentTable,
    rc.name AS ParentColumn,
    fk.name AS ForeignKeyName,
    fk.delete_referential_action_desc AS OnDelete,
    fk.is_disabled AS IsDisabled,
    fk.is_not_trusted AS IsNotTrusted
FROM sys.tables t
JOIN sys.schemas s
    ON s.schema_id = t.schema_id
JOIN sys.columns c
    ON c.object_id = t.object_id
LEFT JOIN sys.indexes pk
    ON pk.object_id = t.object_id
    AND pk.is_primary_key = 1
LEFT JOIN sys.index_columns pkc
    ON pkc.object_id = pk.object_id
    AND pkc.index_id = pk.index_id
    AND pkc.column_id = c.column_id
LEFT JOIN sys.foreign_key_columns fkc
    ON fkc.parent_object_id = c.object_id
    AND fkc.parent_column_id = c.column_id
LEFT JOIN sys.foreign_keys fk
    ON fk.object_id = fkc.constraint_object_id
LEFT JOIN sys.columns rc
    ON rc.object_id = fkc.referenced_object_id
    AND rc.column_id = fkc.referenced_column_id
WHERE t.is_ms_shipped = 0
ORDER BY TableSchema, TableName, c.column_id;

Both queries only read the catalog views, so they’re safe to run on production. They return only the tables you own or have been granted a permission on, such as SELECT or VIEW DEFINITION; any others are left out.

2. Copy the results and paste them

In SSMS, click the top-left corner of the results grid, then right-click and choose Copy with Headers (Ctrl+Shift+C). The SQL Server extension for VS Code has the same option. Results to Text and saved CSV files work too.

Following one table’s relationships

Type part of a table’s name under Show and pick it from the list, or double-click a table in the diagram, to draw it with only the tables related to it. Each table in the list shows how many tables it references and how many reference it. A summary above the diagram lists them, grouped by how many steps away they are. Following chooses which relationships to follow:

• What it references and what references it follows the foreign keys up from the table and down from it, each in its own direction, as far as you choose. It’s the table’s full chain of dependencies. Tables that only share a parent with it, such as other tables that reference the same customer, are left out.
• Only what it references shows the tables its rows depend on: a row of this table can only be inserted once the rows it points to exist, unless its foreign key columns are left NULL. That’s the list to copy first when moving data between environments.
• Only what references it shows the tables with rows that depend on it. These are the tables to check before deleting from it: their rows either block the delete or, with ON DELETE CASCADE or SET NULL, are changed along with it. Following it all the way gives the complete chain a delete can reach.
• Every link, in any direction follows each foreign key both ways at every step, so it also reaches tables that share a parent with it. Followed all the way, that’s every table connected to it at all.

How far limits the number of steps. All the way keeps going until there’s nothing left to follow, and stops at loops, such as a table that references itself.

Reading the diagram

• Parents are on the left. Each table is placed to the right of the tables it references, so lookup and master tables gather on the left and the tables that depend on them on the right.
• Each line joins the columns of a foreign key. A key on more than one column is drawn from its first column; hover over the line or open Relationships shown for all of them.
• The ends of a line use crow’s foot notation. Two bars at the parent mean every child row has exactly one parent. A bar and a circle mean the foreign key columns allow NULL, so a child row can exist without one. The three-pronged foot at the child means many rows. A bar and a circle there mean at most one child row per parent, because the foreign key is the child’s whole primary key: a one-to-one relationship.
• Amber dashed lines are foreign keys that are not trusted: they were created or re-enabled WITH NOCHECK, so SQL Server hasn’t checked the existing rows. The optimizer can’t rely on them, for example to skip a join it doesn’t need. ALTER TABLE ... WITH CHECK CHECK CONSTRAINT ... checks the rows and makes the key trusted again.
• Red dashed lines are disabled foreign keys, which aren’t checked at all.
• A loop on the side of a table is a foreign key to its own table, such as a parent category or a manager.

These three need the second query; with just the foreign keys, every line is drawn as required and trusted.

Notes

• Large databases: choose a table under Show to draw just that table and the tables related to it. Key columns only keeps big tables compact.
• Moving tables: dragging a table only changes the picture. Changing an option redraws the layout from scratch.
• Mermaid: Copy Mermaid gives an erDiagram you can paste into GitHub, GitLab, Notion, or anywhere else that renders Mermaid. Names are changed to letters, digits and underscores, and the schema is joined to the table name with an underscore when there’s more than one schema.
• Share link: the link holds the pasted results, so anyone with it can see the table and column names. For a large schema it can be too long for some chat tools.
• Other shapes: the tool reads any results with recognisable column names, including the output of sp_fkeys. Without a header row, it expects the columns in the order of one of the queries above.
• What’s not shown: views, unique constraints that aren’t referenced, check constraints, and relationships that exist only in application code. If two tables are joined everywhere but have no line between them, that’s a foreign key worth adding.