LocalDict

Guides › Export a SQL Server schema to Markdown

Export a SQL Server schema to Markdown

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

Markdown is a good home for schema documentation: it renders in GitHub, Azure DevOps and most wikis, it diffs cleanly in pull requests, and it's plain text. This page shows how to produce it with nothing but T-SQL.

The query

It builds one Markdown table per user table, with column, type (with length or precision), nullability, primary key and the MS_Description text. It needs SQL Server 2017+ for STRING_AGG, and returns a single nvarchar(max) value.

-- Export every user table as Markdown (SQL Server 2017+ for STRING_AGG).
-- Returns one nvarchar(max) value. Save it with sqlcmd (see below) to avoid SSMS truncation.
WITH cols AS (
    SELECT
        s.name AS sch, t.name AS tbl, t.object_id, c.column_id,
        N'| ' + c.name + N' | ' + TYPE_NAME(c.user_type_id)
        + CASE
            WHEN TYPE_NAME(c.system_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.system_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.system_type_id) IN (N'decimal', N'numeric')
                THEN CONCAT(N'(', c.precision, N',', c.scale, N')')
            ELSE N'' END
        + N' | ' + IIF(c.is_nullable = 1, N'yes', N'no')
        + N' | ' + IIF(EXISTS (SELECT 1 FROM sys.index_columns AS ic
                               JOIN sys.key_constraints AS kc
                                 ON kc.parent_object_id = ic.object_id AND kc.unique_index_id = ic.index_id
                               WHERE kc.type = 'PK' AND ic.object_id = c.object_id AND ic.column_id = c.column_id), N'PK', N'')
        + N' | ' + REPLACE(REPLACE(REPLACE(ISNULL(CAST(ep.value AS nvarchar(4000)), N''),
                   N'|', N'\|'), NCHAR(13), N' '), NCHAR(10), N' ')
        + N' |' AS line
    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 sys.extended_properties AS ep
           ON ep.class = 1 AND ep.major_id = c.object_id
          AND ep.minor_id = c.column_id AND ep.name = N'MS_Description'
    WHERE t.is_ms_shipped = 0
),
blocks AS (
    SELECT sch, tbl,
           N'## ' + sch + N'.' + tbl + NCHAR(10) + NCHAR(10)
           + N'| Column | Type | Null | Key | Description |' + NCHAR(10)
           + N'|---|---|---|---|---|' + NCHAR(10)
           + STRING_AGG(CAST(line AS nvarchar(max)), NCHAR(10)) WITHIN GROUP (ORDER BY column_id) AS block
    FROM cols
    GROUP BY object_id, sch, tbl
)
SELECT N'# Data dictionary: ' + DB_NAME() + NCHAR(10) + NCHAR(10)
       + STRING_AGG(CAST(block AS nvarchar(max)), NCHAR(10) + NCHAR(10)) WITHIN GROUP (ORDER BY sch, tbl)
       AS markdown
FROM blocks;

Sample output

From the pet-clinic sample database:

# Data dictionary: PetClinicGuide

## billing.Order

| Column | Type | Null | Key | Description |
|---|---|---|---|---|
| OrderId | int | no | PK |  |
| OwnerId | int | no |  |  |
| VisitId | bigint | yes |  |  |
| Total | money | no |  |  |
| Status | nvarchar(20) | no |  |  |
| RowVer | timestamp | no |  |  |
| Uid | uniqueidentifier | no |  |  |

Save it to a file (avoid SSMS truncation)

SSMS limits how many characters it shows from a single cell, so long output can be cut off in the grid. Save the query as schema-markdown.sql and run it with sqlcmd instead. -y 0 removes the column width limit and -h -1 drops the header row:

sqlcmd -S localhost -d YourDatabase -E -i schema-markdown.sql -o data-dictionary.md -y 0 -h -1

Use -U/-P instead of -E for SQL logins. Commit the file next to your database project and re-run it when the schema changes.

Notes

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