LocalDict

Guides › SQL Server ER diagram from catalog views with Mermaid

SQL Server ER diagram from catalog views with Mermaid

Updated 2026-11-21 · Every script on this page was run on SQL Server 2022 against a sample pet-clinic database.

Mermaid draws diagrams from text. GitHub and GitLab render a ```mermaid block automatically, and Azure DevOps wikis, Notion and many docs tools support Mermaid too, so a text ER diagram can live in the same repo as your schema. SQL Server already knows every relationship, so we can generate the text.

The query

It lists each table as an entity with its columns (marked PK/FK) and adds one relationship line per foreign key. It needs SQL Server 2017+ for STRING_AGG.

-- Generate a Mermaid erDiagram from the catalog views (SQL Server 2017+).
-- Paste the result into any Mermaid viewer, a GitHub Markdown ```mermaid block, or LocalDict.
DECLARE @nl nchar(1) = NCHAR(10);

WITH ent AS (
    SELECT s.name + N'_' + t.name AS id, t.object_id,
           N'  ' + REPLACE(s.name + N'_' + t.name, N' ', N'_') + N' {' + @nl
           + STRING_AGG(CAST(
                 N'    ' + REPLACE(TYPE_NAME(c.user_type_id), N' ', N'_') + N' ' + REPLACE(c.name, N' ', N'_')
                 + CASE WHEN pk.column_id IS NOT NULL AND fk.parent_column_id IS NOT NULL THEN N' PK,FK'
                        WHEN pk.column_id IS NOT NULL THEN N' PK'
                        WHEN fk.parent_column_id IS NOT NULL THEN N' FK' ELSE N'' END
             AS nvarchar(max)), @nl) WITHIN GROUP (ORDER BY c.column_id)
           + @nl + N'  }' AS block
    FROM sys.tables AS t
    JOIN sys.schemas AS s ON s.schema_id = t.schema_id
    JOIN sys.columns AS c ON c.object_id = t.object_id
    LEFT JOIN (SELECT ic.object_id, ic.column_id
               FROM sys.key_constraints AS kc
               JOIN sys.index_columns AS ic
                 ON ic.object_id = kc.parent_object_id AND ic.index_id = kc.unique_index_id
               WHERE kc.type = 'PK') AS pk
           ON pk.object_id = c.object_id AND pk.column_id = c.column_id
    LEFT JOIN (SELECT DISTINCT parent_object_id, parent_column_id FROM sys.foreign_key_columns) AS fk
           ON fk.parent_object_id = c.object_id AND fk.parent_column_id = c.column_id
    WHERE t.is_ms_shipped = 0
    GROUP BY s.name, t.name, t.object_id
),
rel AS (
    SELECT N'  ' + REPLACE(rs.name + N'_' + rt.name, N' ', N'_')
           + N' ||--o{ '
           + REPLACE(ps.name + N'_' + pt.name, N' ', N'_')
           + N' : "' + f.name + N'"' AS line
    FROM sys.foreign_keys AS f
    JOIN sys.tables  AS pt ON pt.object_id = f.parent_object_id
    JOIN sys.schemas AS ps ON ps.schema_id = pt.schema_id
    JOIN sys.tables  AS rt ON rt.object_id = f.referenced_object_id
    JOIN sys.schemas AS rs ON rs.schema_id = rt.schema_id
)
SELECT N'erDiagram' + @nl
       + (SELECT STRING_AGG(CAST(block AS nvarchar(max)), @nl) WITHIN GROUP (ORDER BY id) FROM ent)
       + ISNULL(@nl + (SELECT STRING_AGG(CAST(line AS nvarchar(max)), @nl) FROM rel), N'')
       AS mermaid;

Sample output

erDiagram
  billing_Order {
    int OrderId PK
    int OwnerId FK
    bigint VisitId FK
    money Total
    nvarchar Status
    timestamp RowVer
    uniqueidentifier Uid
  }
  billing_Order_Line {
    int OrderId PK,FK
  ...
  clinic_Vet ||--o{ clinic_Referral : "FK_Referral_ToVet"
  clinic_Room ||--o{ clinic_Visit : "FK_Visit_Room"
  clinic_Visit ||--o{ billing_Order : "FK_Order_Visit"
  billing_Order ||--o{ billing_Order_Line : "FK_OrderLine_Order"

Paste it into a Markdown file inside a ```mermaid fence, into the Mermaid live editor, or into LocalDict, which draws it in the browser.

Reading the notation

Limits

Prefer not to hand-roll it?

LocalDict turns one catalog query into a searchable dictionary, Markdown and a Mermaid ER diagram, entirely in your browser. Nothing is uploaded. It's free up to 15 tables; Pro is $24 one-time for unlimited tables.

Open the appTry the demo

Related guides