LocalDict

Guides › Find undocumented columns with MS_Description

Find undocumented columns with MS_Description

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

Most databases have some column descriptions. The useful question is which tables have none and how much is left. These two read-only queries answer that, and the second writes the boilerplate to fix it.

Documentation coverage per table

This query lists the least-documented tables first:

-- Columns with no MS_Description, plus documentation coverage per table.
SELECT
    s.name AS [schema],
    t.name AS [table],
    COUNT(*)                                            AS columns_total,
    SUM(IIF(ep.value IS NULL, 1, 0))                    AS columns_undocumented,
    CAST(100.0 * SUM(IIF(ep.value IS NULL, 0, 1)) / COUNT(*) AS decimal(5,1)) AS pct_documented,
    IIF(tp.value IS NULL, 'no', 'yes')                  AS table_has_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'
LEFT JOIN sys.extended_properties AS tp
       ON tp.class = 1 AND tp.major_id = t.object_id
      AND tp.minor_id = 0 AND tp.name = N'MS_Description'
WHERE t.is_ms_shipped = 0
GROUP BY s.name, t.name, tp.value
ORDER BY pct_documented, s.name, t.name;

Sample result (schema, table, total, undocumented, % documented, table description):

billing	Order	7	7	0	yes
billing	Order Line	5	5	0	yes
clinic	Referral	4	4	0	no
clinic	Room	3	3	0	yes
clinic	Species	2	2	0	yes
clinic	Tag	6	6	0	no
clinic	Vet	3	3	0	no
dbo	AppSetting	2	2	0	no
clinic	Visit	9	8	11.1	yes
clinic	Owner	5	3	40	yes
clinic	Pet	10	6	40	yes

Generate sp_addextendedproperty stubs

This query writes one EXEC statement per undocumented column. Copy the output into a new window, replace each TODO with a real description, delete the lines you don't want, and run it. Names with apostrophes are escaped.

-- Generate sp_addextendedproperty statements for every undocumented column.
-- Copy the output into a new query window, replace each TODO, then run it.
SELECT
    N'EXEC sys.sp_addextendedproperty @name = N''MS_Description'', @value = N''TODO: describe '
    + REPLACE(c.name, N'''', N'''''') + N''', '
    + N'@level0type = N''SCHEMA'', @level0name = N''' + REPLACE(s.name, N'''', N'''''') + N''', '
    + N'@level1type = N''TABLE'', @level1name = N''' + REPLACE(t.name, N'''', N'''''') + N''', '
    + N'@level2type = N''COLUMN'', @level2name = N''' + REPLACE(c.name, N'''', N'''''') + N''';'
    AS statement
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
WHERE t.is_ms_shipped = 0
  AND NOT EXISTS (SELECT 1 FROM sys.extended_properties AS ep
                  WHERE ep.class = 1 AND ep.major_id = c.object_id
                    AND ep.minor_id = c.column_id AND ep.name = N'MS_Description')
ORDER BY s.name, t.name, c.column_id;

Output looks like this:

EXEC sys.sp_addextendedproperty @name = N'MS_Description', @value = N'TODO: describe OrderId', @level0type = N'SCHEMA', @level0name = N'billing', @level1type = N'TABLE', @level1name = N'Order', @level2type = N'COLUMN', @level2name = N'OrderId';
EXEC sys.sp_addextendedproperty @name = N'MS_Description', @value = N'TODO: describe OwnerId', @level0type = N'SCHEMA', @level0name = N'billing', @level1type = N'TABLE', @level1name = N'Order', @level2type = N'COLUMN', @level2name = N'OwnerId';

To change an existing description, use sys.sp_updateextendedproperty with the same arguments. sp_addextendedproperty fails if the property already exists.

Tips for descriptions people actually read

Once descriptions exist, every tool that reads MS_Description picks them up: SSMS, database projects, and LocalDict, which also counts the columns still missing a description.

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