The wiki says a column means one thing, while the procedure uses it another way. Database documentation stays useful when it travels with the schema change.
Describe the Decision Where It Lives
A good description explains business meaning, units, allowed values, and why a rule exists. A column named Status needs more than “current status.” Write what each value means and who owns the definition. Keep the description close to the database object so a later change exposes the mismatch.
I have seen a polished document outlive three schema changes without anyone opening it. The database and the document then disagree. That is a process problem, not a formatting problem. Put documentation updates into the same review as the DDL change.
What would a new DBA need to know before changing this column? Answer that in plain words. If the answer is a long operational runbook, keep the runbook in your controlled document location and put a short pointer in the database record.
Use Extended Properties for Object Meaning
SQL Server extended properties can attach descriptions to schemas, tables, columns, and other objects. MS_Description is a common property name for readable descriptions. The property is stored with database metadata, so it can travel with a database backup. A deployment that recreates an object still needs to reapply its intended description.
Use sp_addextendedproperty to add a property and sp_updateextendedproperty to change one. Include those statements in the same deployment package as the object change. Do not expect a person to remember a separate documentation task after the release.
I keep descriptions short and specific. “Customer identifier from billing system” helps. “ID column” does not. No one needs a catalog view to learn that.
Read Properties From the Catalog
sys.extended_properties stores the property name and value with class and object identifiers. Join it to sys.objects and sys.columns for table and column descriptions. A property on a schema or another class needs a different join, so label the scope of your report.
The query below lists properties attached to user tables and their columns in the current database. A NULL column name indicates a table level property. Use the class filter to avoid pretending the same major_id means a table in every property class.
This is a live report. Regenerate it after deployment. A copied spreadsheet has a shelf life; the catalog query does not forget to look again.
SELECT
SCHEMA_NAME(o.schema_id) AS SchemaName,
o.name AS TableName,
c.name AS ColumnName,
ep.name AS PropertyName,
CONVERT(nvarchar(max), ep.value) AS PropertyValue
FROM sys.extended_properties AS ep
JOIN sys.objects AS o
ON o.object_id = ep.major_id
LEFT JOIN sys.columns AS c
ON c.object_id = ep.major_id
AND c.column_id = ep.minor_id
WHERE ep.class = 1
AND o.type = 'U'
ORDER BY SchemaName, TableName, ep.minor_id, ep.name;Keep Useful Comments in Modules
A stored procedure can carry comments that explain a nonobvious calculation or a safety check. Those comments live in the module definition when deployed as part of the CREATE or ALTER script. Keep them with the code change. A separate note that describes an old formula will mislead the next reader.
Do not narrate every line. Explain why a branch exists, which assumption it protects, and which external contract it serves. Comments that merely repeat the SQL add noise. A short reason is more valuable than a paragraph of history without a date or owner.
I read sys.sql_modules when a comment and the procedure behavior appear to disagree. The deployed definition is the code users run. The deployment package should match it after review.
Generate Current Database Documentation
A catalog-generated document can list schemas, tables, columns, data types, nullability, and extended descriptions. Build it from sys.objects, sys.columns, sys.types, and sys.extended_properties. Add a capture time and database name. Those fields tell readers whether the report describes the environment they are working on.
A generated report cannot invent business meaning. Missing descriptions should appear as missing, not as a generic phrase. Give object owners a short queue of gaps to fill. The report then helps improve documentation rather than hiding its absence.
The query below gives a structural starting point. Join descriptions as needed for your own report, and review the output before sharing.
SELECT
DB_NAME() AS DatabaseName,
SCHEMA_NAME(o.schema_id) AS SchemaName,
o.name AS TableName,
c.column_id,
c.name AS ColumnName,
t.name AS TypeName,
c.is_nullable
FROM sys.objects AS o
JOIN sys.columns AS c
ON c.object_id = o.object_id
JOIN sys.types AS t
ON t.user_type_id = c.user_type_id
WHERE o.type = 'U'
ORDER BY SchemaName, TableName, c.column_id;Make Deployment Preserve the Database Documentation
A migration script should apply DDL and its matching extended property update together. Test both on a restored database. Verify the object and the property after deployment. A rename can leave a description attached to a different meaning if nobody reviews it.
Module comments need the same care. A deployment that regenerates procedures from a template can drop comments if the template omits them. Check the deployed definition, not only the source file. The production database is the final result.
I add documentation checks to the change review for columns with business meaning. It is cheaper to ask during design than after a report owner finds a misleading label.
Separate Structure From Operations
The catalog is good at schema facts. It cannot fully explain a nightly business process, an approval rule, or a recovery decision. Keep those runbooks in an accessible operations location. Put a short reference beside the related database object when it helps readers find the larger document.
Avoid URLs in object descriptions that depend on a temporary personal folder. Use a stable internal document identifier or maintained location. A broken pointer is another form of stale documentation.
Ask the owner to review meaning when the application changes. Schema can stay constant while the business definition shifts. A property that says “active customer” needs a real definition of active.
Review Database Documentation Like Code
During a change, compare the old and new object definition and description. Confirm the text still matches behavior. After deployment, query the catalog and inspect the result. Add the capture date to generated documents so nobody mistakes an old export for the live database.
If a description is wrong, fix it in the deployment process and in the current database through the approved change. Do not edit a report only. Otherwise the next generation will reproduce the old error.
The best database documentation is easy to update at the moment knowledge changes. Keep the words near the object and make the report reproducible.
Related reading on this blog: Document Your Databases with Data Dictionary and Diagrams and How to Automatically Generate SQL Server Documentation ?.
Database documentation is not a separate book, it is part of the change that gives the schema meaning.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
