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
- clinic_Owner ||--o{ clinic_Pet means one owner has zero or more pets.
- Entity names are schema_table, with spaces replaced by underscores, because Mermaid entity names can't contain dots or spaces.
- If a table has two foreign keys to the same parent, as with FromVetId and ToVetId above, you get two labelled lines.
Limits
- Large databases (40+ tables) produce diagrams that are hard to read. Filter the query with WHERE s.name = N'clinic' to diagram one schema at a time.
- Table or column names containing characters other than letters, digits, underscores and spaces (for example hyphens) need extra REPLACE calls.
- All relationships are drawn as "one to zero-or-more". LocalDict's export also marks nullable foreign keys as optional (|o--o{).
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