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 yesGenerate 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
- Say what the value means and its unit, not its type: "Weight in kilograms at the last visit", not "decimal weight".
- List allowed values for status-like columns: "Open, Paid or Void".
- Note the source of truth for copied or derived columns.
- Don't document surrogate keys at length. "Surrogate key." is fine.
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