Building a Data Dictionary Query From Extended Properties

A good data dictionary query shows the columns nobody has described yet, not just the ones somebody did. The trick is a LEFT JOIN from your columns to the extended properties. Use a plain join, and every undocumented column quietly vanishes from the report.

Why a data dictionary starts as a query

A new teammate opens the customer table and asks, “What does IsActive mean? Active in what sense?” Everyone nods and says, “Ask the person who built it.” Then that person changes teams. That is the day you wish the answer lived next to the column.

SQL Server has a place for it. An extended property named MS_Description can sit on a table or a column. You write the text once, and a query can read it back for the whole database.

Create a table and describe two things

The demo table has three columns. Only the table and CustomerName get a description. Run it in a practice database, because the queries read every user table. The demo creates one table and one view and removes both at the end.

DROP TABLE IF EXISTS dbo.CustomerDemo;

CREATE TABLE dbo.CustomerDemo
(
    CustomerId   int NOT NULL PRIMARY KEY,
    CustomerName nvarchar(80) NULL,
    IsActive     bit NOT NULL DEFAULT (1)
);

EXEC sys.sp_addextendedproperty
    @name = N'MS_Description', @value = N'Customer reference data',
    @level0type = N'SCHEMA', @level0name = N'dbo',
    @level1type = N'TABLE',  @level1name = N'CustomerDemo';

EXEC sys.sp_addextendedproperty
    @name = N'MS_Description', @value = N'Name used for display, not the customer key',
    @level0type = N'SCHEMA', @level0name = N'dbo',
    @level1type = N'TABLE',  @level1name = N'CustomerDemo',
    @level2type = N'COLUMN', @level2name = N'CustomerName';

Read structure and meaning together

One query now gives the whole picture. It lists every column of every user table with its type, size, nullability and default. Then it adds the column description and the table description when they exist.

SELECT s.name AS schema_name, t.name AS table_name,
       c.column_id, c.name AS column_name,
       TYPE_NAME(c.user_type_id) AS type_name, c.max_length, c.is_nullable,
       dc.definition AS default_definition,
       CONVERT(nvarchar(max), ep.value) AS column_description,
       CONVERT(nvarchar(max), tp.value) AS table_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.default_constraints AS dc ON dc.object_id = c.default_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 = c.object_id
      AND tp.minor_id = 0 AND tp.name = N'MS_Description'
WHERE t.is_ms_shipped = 0
ORDER BY s.name, t.name, c.column_id;

All three columns come back. CustomerName shows its description, and the other two show NULL. Look at max_length for CustomerName. It says 160, not 80, because nvarchar uses two bytes per character. The column is in bytes, so do not read it as a character count.

Now the mistake I promised. Change the two LEFT JOINs to plain JOINs and count the rows. A plain join keeps only columns that have a property, so the report looks tidy and is wrong.

SELECT COUNT(*) AS rows_with_inner_join
FROM sys.columns AS c
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 c.object_id = OBJECT_ID(N'dbo.CustomerDemo');

SELECT COUNT(*) AS rows_with_left_join
FROM sys.columns AS c
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 c.object_id = OBJECT_ID(N'dbo.CustomerDemo');

The plain join returns 1 row. The LEFT JOIN returns 3. Two columns just disappeared, and nobody would notice.

Turn the gaps into a to-do list

Once the gaps are visible, you can list them. I put the check in a small view, so you can run it before and after you write descriptions. A blank string counts as missing too.

CREATE OR ALTER VIEW dbo.MissingColumnDescriptions
AS
SELECT s.name AS schema_name, t.name AS table_name, c.name AS column_name
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
  AND NULLIF(LTRIM(RTRIM(CONVERT(nvarchar(max), ep.value))), N'') IS NULL;
GO
SELECT column_name FROM dbo.MissingColumnDescriptions
ORDER BY schema_name, table_name, column_name;

The list shows CustomerId and IsActive. Write the missing description, then run the check again.

EXEC sys.sp_addextendedproperty
    @name = N'MS_Description', @value = N'1 means the customer can place orders',
    @level0type = N'SCHEMA', @level0name = N'dbo',
    @level1type = N'TABLE',  @level1name = N'CustomerDemo',
    @level2type = N'COLUMN', @level2name = N'IsActive';

SELECT column_name FROM dbo.MissingColumnDescriptions
ORDER BY schema_name, table_name, column_name;

Now only CustomerId is left. That is the whole workflow: list the gaps, write one description, list again.

What a dictionary can and cannot promise

A description is just text somebody typed. It is not a verified fact, and it can go stale. Ask an owner to review old entries, and do not trust a string because it is not empty.

Run the query with the same permissions your report will use. Metadata you cannot see is silently left out. If you publish the result as a web page, escape the descriptions as text.

DROP VIEW IF EXISTS dbo.MissingColumnDescriptions;
DROP TABLE IF EXISTS dbo.CustomerDemo;

Start with your busiest table, and let the gaps list tell you what to write next.

A data dictionary is not a list of columns, it is a list of answers.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.