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
- sys.tables and sys.schemas: table and schema names.
- sys.columns: column name, position, type, length, nullability, identity and computed flags.
- sys.key_constraints + sys.index_columns: primary keys and unique constraints.
- sys.foreign_keys + sys.foreign_key_columns: relationships.
- sys.extended_properties: descriptions, by convention stored under the name MS_Description. SSMS's table designer writes the same property.
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:
- Paste the grid into a spreadsheet. It's fast, but it goes stale and is hard to search across tables.
- Generate Markdown directly from T-SQL. See Export a SQL Server schema to Markdown.
- Generate an ER diagram. See SQL Server ER diagram with Mermaid.
- Use the full LocalDict catalog query. It adds keys, foreign keys, defaults, identity and alias types, and you can paste the result into LocalDict for a searchable HTML dictionary.
Gotchas
- Alias types (for example CREATE TYPE dbo.Name FROM nvarchar(50)) show their alias name in TYPE_NAME(user_type_id). Use TYPE_NAME(system_type_id) for the underlying type.
- Some databases use a property named Description instead of MS_Description. Check with SELECT DISTINCT name FROM sys.extended_properties;
- Descriptions can contain line breaks, which break copy-paste from the SSMS grid. Wrap the value in REPLACE(..., CHAR(10), ' ') if you copy results.
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