What is a good way to document what your tables and columns mean in a SQL Server database?

Question

I'm working on a C# project using a SQL Server 2008 database. There are 50+ tables in the database and from the name alone, the purpose of these tables and the columns within them aren't immediately obvious. I want to create some form of documentation so future team members will know what the columns and tables do.

I'm looking for the SQL Server equivalent of C# "code comments" or "XML documentation on a method" - something a new person can glance at to gain understanding of a database table or column.

What are some options?

Answer 1

You can add extended properties

Viewing Extended Properties

e.g. displays all extended properties in the AdventureWorks2008R2 sample database

USE AdventureWorks2008R2;
GO
SELECT class, class_desc, major_id, minor_id, name, value
FROM sys.extended_properties;
GO

Fun with Extended Properties in SQL Server 2008

Answer 2

A simple approach to document table columns is to use the SQL Server Management Studio table designer. Fill in the Description field for each column.

Then you can view table info with a query joining the sys.extended_properties table:

-- list table columns
SELECT OBJECT_NAME(c.OBJECT_ID) [TableName], c.name [ColumnName], t.name [DataType], x.Value [Description], c.max_length [Length], c.Precision ,c.Scale
FROM sys.columns AS c
JOIN sys.types AS t ON c.user_type_id=t.user_type_id
LEFT JOIN sys.extended_properties x on x.major_id = c.object_id and x.minor_id = c.column_id
WHERE OBJECT_NAME(c.OBJECT_ID) = 'Employees'
ORDER BY c.OBJECT_ID, c.column_id;

Answer 3

Extended properties? Can be set in code or in the GUI

We also use Red Gate SQL Doc to publish our schema too and extended properties appear in the descriptions here. Very useful.