Documenting a Database With Extended Properties

The wiki says a column holds dollars, but the application stores cents. Keeping descriptions in extended properties puts that explanation beside the schema you actually query. You can maintain them with stored procedures and inspect missing documentation with SQL.

Describe Meaning Before Mechanics

A column name tells you where to look. A useful description tells you what the value means. Include units, time zone, allowed states, and any rule that the type cannot express. CustomerID needs less explanation than an amount whose currency comes from another table. Describe that relationship, rather than repeating the column name with spaces.

I check units before writing a dictionary query. Almost every confusing description I review repeats the type instead of explaining the business rule. The database already knows that an integer is an integer. It does not know that zero means an account has never been scored. That distinction saves a reader an unnecessary trip through application code.

Add Extended Properties to a Sample Table

Run the examples in a disposable database. They create one permanent demonstration table because these properties belong to database objects. The standard description name is MS_Description. Properties are named values, and that name is a convention used by several tools. SQL Server does not enforce the truth of the prose.

CREATE TABLE dbo.Invoice
(
    InvoiceID int NOT NULL CONSTRAINT PK_Invoice PRIMARY KEY,
    TotalCents bigint NOT NULL,
    IssuedAtUtc datetime2(0) NOT NULL
);
EXEC sys.sp_addextendedproperty
    @name=N'MS_Description', @value=N'Customer invoices with totals in US cents.',
    @level0type=N'SCHEMA', @level0name=N'dbo',
    @level1type=N'TABLE', @level1name=N'Invoice';
EXEC sys.sp_addextendedproperty
    @name=N'MS_Description', @value=N'Invoice total in US cents before refunds.',
    @level0type=N'SCHEMA', @level0name=N'dbo',
    @level1type=N'TABLE', @level1name=N'Invoice',
    @level2type=N'COLUMN', @level2name=N'TotalCents';

Schema, table, and column identify the target precisely. Do not rely on an unqualified name in a deployment. Use the same schema spelling as your DDL. A property at the table level cannot substitute for an explanation attached to a particular column. Keep both when they answer different questions.

Keep each description short enough to read alongside a query result. Explain the important rule first, then add an example only when it removes ambiguity. Avoid embedding a long operational manual in one property value. Store stable meaning here and maintain broader procedures in the team's documentation. A table description can point readers toward a named internal process without turning the database annotation into another document nobody updates.

Change or Remove the Stored Description

Adding the same property twice fails. Updating a missing property also fails. Deployment code must choose the operation based on current metadata, or maintain an explicit migration sequence. I prefer deliberate migrations when the description changes with a business rule. That makes the reason for the change visible during review.

EXEC sys.sp_updateextendedproperty
    @name=N'MS_Description',
    @value=N'Invoice total in US cents after discounts and before refunds.',
    @level0type=N'SCHEMA', @level0name=N'dbo',
    @level1type=N'TABLE', @level1name=N'Invoice',
    @level2type=N'COLUMN', @level2name=N'TotalCents';
EXEC sys.sp_addextendedproperty
    @name=N'ReviewNote', @value=N'Confirm refund treatment during review.',
    @level0type=N'SCHEMA', @level0name=N'dbo',
    @level1type=N'TABLE', @level1name=N'Invoice';
EXEC sys.sp_dropextendedproperty
    @name=N'ReviewNote',
    @level0type=N'SCHEMA', @level0name=N'dbo',
    @level1type=N'TABLE', @level1name=N'Invoice';

The drop removes only the named annotation. It does not drop the table or change values. Permissions still matter. Give the deployment identity the appropriate object authority, rather than handing every reader schema modification rights. Descriptions should also exclude passwords, secrets, and personal records. Metadata travels with copies of the database.

Read Extended Properties Two Ways

The listing function is convenient when you know the target. The catalog view is better for a dictionary spanning many tables. Both expose the property value as sql_variant. Convert it to a readable string for output. A missing property produces no function row, rather than an empty description row.

SELECT objtype,objname,name,CONVERT(nvarchar(4000),value) AS Description
FROM sys.fn_listextendedproperty
    (N'MS_Description',N'SCHEMA',N'dbo',N'TABLE',N'Invoice',N'COLUMN',NULL);
SELECT ep.major_id,ep.minor_id,ep.name,
       CONVERT(nvarchar(4000),ep.value) AS Description
FROM sys.extended_properties AS ep
WHERE ep.class=1
  AND ep.major_id=OBJECT_ID(N'dbo.Invoice')
  AND ep.name=N'MS_Description';

For object and column properties, class is one. A minor_id of zero describes the table. A positive minor_id identifies a column. Include that class predicate in joins because other property classes use identifiers differently. Otherwise a plausible description can be attached to the wrong row in your report.

Build a Dictionary That Keeps Undescribed Columns

Start from tables and columns, then left join descriptions. Starting from properties hides everything nobody documented. Include the declared type and its dimensions. SQL Server records nvarchar lengths in bytes, so divide those lengths by two. MAX appears as minus one in metadata and needs its own label.

SELECT s.name AS SchemaName,t.name AS TableName,c.column_id,c.name AS ColumnName,
       ty.name AS TypeName,
       CASE WHEN c.max_length=-1 THEN N'MAX'
            WHEN ty.name IN(N'nchar',N'nvarchar')
                 THEN CONVERT(nvarchar(10),c.max_length/2)
            ELSE CONVERT(nvarchar(10),c.max_length) END AS DeclaredLength,
       c.precision,c.scale,c.is_nullable,
       CONVERT(nvarchar(4000),tp.value) AS TableDescription,
       CONVERT(nvarchar(4000),cp.value) AS ColumnDescription
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
JOIN sys.types AS ty ON ty.user_type_id=c.user_type_id
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'
LEFT JOIN sys.extended_properties AS cp
  ON cp.class=1 AND cp.major_id=c.object_id AND cp.minor_id=c.column_id
 AND cp.name=N'MS_Description'
WHERE t.is_ms_shipped=0
ORDER BY s.name,t.name,c.column_id;

The length field is metadata, not a universal character capacity. Decimal types use precision and scale. Alias types deserve their schema and underlying type in an expanded dictionary. Add identity, computed expression, defaults, and references when your readers need them. Keep those joins separate enough to avoid multiplying one column into several rows.

Find the Blank Spaces

An absent annotation and an annotation containing only spaces both leave readers guessing. This check returns those gaps. Run it under an identity that can see the intended schema. Catalog visibility follows permissions, so a restricted reader's empty report cannot prove every table is documented.

SELECT s.name AS SchemaName,t.name AS TableName,c.name AS ColumnName
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(4000),ep.value))),N'') IS NULL
ORDER BY s.name,t.name,c.column_id;

Which missing explanation would cause a wrong calculation tomorrow? Start there. A percentage of annotated columns measures coverage, but not accuracy. A database full of descriptions saying important field scores beautifully and helps nobody. Documentation has discovered a way to look busy without leaving its chair.

Review Extended Properties With Schema Changes

Keeping extended properties in the database removes one source of separation. It does not prevent stale words. A change from local time to UTC still requires a description change. Put annotation DDL beside column DDL in the same reviewed migration. Check that schema comparison and extraction tools include these properties.

I review descriptions when a schema review changes meaning, regardless of whether the type changes. A new status interpretation can invalidate a note without changing one byte of schema. Ask the application owner to verify the business statement. A DBA can explain storage precisely and still need that confirmation about billing rules.

Export From the Schema You Deployed

Generate a readable dictionary from the deployed database after a release. That gives readers one current view and reduces manual copying. Preserve the reviewed property scripts with the release artifacts. Compare important descriptions between environments when support reports conflicting behavior. The report should identify its database and collection time outside the stored description itself.

Treat the dictionary as an aid to investigation. Constraints, tests, and application rules remain necessary. If the note says an amount is nonnegative, add the corresponding check when that rule is valid. Extended properties explain intent, while executable rules enforce the parts SQL Server can test.

Related reading on this blog: Keeping Database Documentation Next to the Code and Document Your Databases with Data Dictionary and Diagrams.

A database description is not decoration, it is context for the next decision.

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