LocalDict

Guides › How to generate a data dictionary in SQL Server

How to generate a data dictionary in SQL Server

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

A data dictionary lists every table and column in a database with its type, whether it allows NULLs, its keys and a plain-English description. SQL Server already stores all of that in its catalog views. You don't need a commercial tool to read it, just a query.

Where the information lives

Step 1: a quick dictionary query

This read-only query returns one row per column, with its description. It works on SQL Server 2016+ and Azure SQL Database, and it reads metadata only, never table data.

-- Quick data dictionary: one row per column, with its MS_Description.
SELECT
    s.name                            AS [schema],
    t.name                            AS [table],
    c.column_id                       AS [ordinal],
    c.name                            AS [column],
    TYPE_NAME(c.user_type_id)         AS [data_type],
    c.max_length,
    c.is_nullable,
    CAST(ep.value AS nvarchar(4000))  AS [description]
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
ORDER BY s.name, t.name, c.column_id;

On a small pet-clinic sample it returns rows like these:

billing	Order	1	OrderId	int	4	0	
billing	Order	2	OwnerId	int	4	0	
billing	Order	3	VisitId	bigint	8	1	
billing	Order	4	Total	money	8	0	
billing	Order	5	Status	nvarchar	40	0	
billing	Order	6	RowVer	timestamp	8	0	

Note that max_length is in bytes, so nvarchar(120) shows as 240, and -1 means max.

Step 2: add the missing descriptions

A dictionary is only as good as its descriptions. Add them as extended properties so they live with the database and survive deployments. Database projects and most comparison tools carry them along.

-- Add (or update) a description on a table and on a column.
EXEC sys.sp_addextendedproperty
     @name = N'MS_Description', @value = N'Animals seen at the clinic.',
     @level0type = N'SCHEMA', @level0name = N'clinic',
     @level1type = N'TABLE',  @level1name = N'Pet';

EXEC sys.sp_addextendedproperty
     @name = N'MS_Description', @value = N'Name the owner uses for the pet.',
     @level0type = N'SCHEMA', @level0name = N'clinic',
     @level1type = N'TABLE',  @level1name = N'Pet',
     @level2type = N'COLUMN', @level2name = N'Name';

-- Already has one? Use sp_updateextendedproperty with the same arguments.

To see which columns still need one, use the coverage query in Find undocumented columns with MS_Description.

Step 3: turn the rows into a document

You have a few options:

Gotchas

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