Guide
Build a SQL Server data dictionary from sys.columns and MS_Description extended properties, export it, and keep it from going stale after the next release.
A data dictionary is the document someone asks for in the week a new analyst joins, an auditor arrives or a vendor needs to integrate: every table, every column, its type, whether it can be null, and a sentence that explains what it means. SQL Server already holds most of that information in its catalog views. The part it does not hold is the sentence, unless somebody wrote it into an extended property.
This returns one row per column with the type spelled the way people read it, the nullability, the default, the primary key flag and the MS_Description text if there is one:
SELECT
s.name AS schema_name,
t.name AS table_name,
c.column_id,
c.name AS column_name,
ty.name +
CASE
WHEN ty.name IN ('varchar','char','varbinary','binary')
THEN '(' + IIF(c.max_length = -1, 'max', CAST(c.max_length AS varchar(10))) + ')'
WHEN ty.name IN ('nvarchar','nchar')
THEN '(' + IIF(c.max_length = -1, 'max', CAST(c.max_length / 2 AS varchar(10))) + ')'
WHEN ty.name IN ('decimal','numeric')
THEN '(' + CAST(c.precision AS varchar(5)) + ',' + CAST(c.scale AS varchar(5)) + ')'
ELSE ''
END AS data_type,
c.is_nullable,
dc.definition AS default_value,
IIF(pk.column_id IS NULL, 0, 1) AS is_primary_key,
CAST(ep.value AS nvarchar(4000)) AS description
FROM sys.tables t
JOIN sys.schemas s ON s.schema_id = t.schema_id
JOIN sys.columns c ON c.object_id = t.object_id
JOIN sys.types ty ON ty.user_type_id = c.user_type_id
LEFT JOIN sys.default_constraints dc
ON dc.parent_object_id = c.object_id AND dc.parent_column_id = c.column_id
LEFT JOIN (
SELECT ic.object_id, ic.column_id
FROM sys.indexes i
JOIN sys.index_columns ic ON ic.object_id = i.object_id AND ic.index_id = i.index_id
WHERE i.is_primary_key = 1
) pk ON pk.object_id = c.object_id AND pk.column_id = c.column_id
LEFT JOIN sys.extended_properties ep
ON ep.major_id = c.object_id AND ep.minor_id = c.column_id
AND ep.class = 1 AND ep.name = 'MS_Description'
WHERE t.is_ms_shipped = 0
ORDER BY s.name, t.name, c.column_id;
Run it in Management Studio, right click the grid and save as CSV, or paste it into Excel, and the first version of the dictionary exists. Foreign keys come from sys.foreign_key_columns joined back to sys.columns on both sides, which is worth a second query rather than more joins in this one.
MS_Description is the extended property that Management Studio's table designer writes when someone fills in the Description field. It can be set directly:
EXEC sys.sp_addextendedproperty
@name = N'MS_Description',
@value = N'Order total in EUR including VAT, after discounts.',
@level0type = N'SCHEMA', @level0name = N'dbo',
@level1type = N'TABLE', @level1name = N'orders',
@level2type = N'COLUMN', @level2name = N'total';
Keeping descriptions in the database has one real advantage: they travel with backups and restores. The disadvantages are that sp_addextendedproperty fails if the property already exists, so updates need sp_updateextendedproperty, and that almost nobody outside the DBA team has the rights or the habit to write them.
The query is accurate the moment it runs. The spreadsheet saved from it starts drifting at the next deployment: a new column appears with no description, a type is widened, a table is renamed. Nobody re-runs the query until the next audit, and by then the descriptions that were typed into Excel rather than into MS_Description no longer line up with the columns they described.
The fix is to stop treating the dictionary as an export. It should be regenerated from the catalog every time the schema changes, with the human descriptions stored separately so regeneration does not erase them.
That is how Taavik's documentation works for SQL Server. An agent inside the network reads the catalog with a read-only login on a schedule, and each scan regenerates the document: per table, every column with its type, nullability, default, the MS_Description text and the date of its last change, followed by the indexes with primary key and unique flags. Notes written in the workspace attach to a database, schema, table or column and survive every regeneration, so the description someone added last month is still next to its column after the release.
When the schema changes, a new revision is created with a diff, which doubles as a record of what changed between versions. The document can be shared through a public link or copied as HTML into a wiki. Foreign keys are drawn in the schema map rather than listed in the column table, and the map exports to Mermaid, DBML or SQL.
What it does not produce is an Excel or PDF file. Teams that must hand an auditor a spreadsheet can still run the query above for that snapshot; the living version is the one the team actually reads day to day.
Guides for the other engines: PostgreSQL data dictionary and MySQL data dictionary.