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

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开发者_Go百科 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?

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

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;

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.

Documentation is a whole skill set by itself. Good naming methodologies are good, as are consistency so that there aren't any changes across the database in regards to your style.

Besides using SQL Diagrams or something, the best thing is a good document that explains the tables and columns. Define the overall structure and relationships between objects/tables/columns. Give explanations if concepts are more complicated, but make it easy for someone to say 'what does this column do?' and find that answer quick without having to read through a paragraph or two to understand.

Are you willing to investigate (and possibly later invest in) a separate tool for this job??

If so - have a look at these two:

• Red-Gate SQL Doc (browse their Sample AdventureWorks HTML doc online)
• ApexSQL Doc

Both aren't free - they do cost a bit of money upfront - but both create excellent quality documentation, in HTML, PDF, CHM format - your choice. They also get every little piece of information from the database - all the relationships between objects, constraints and default values on columns, and a lot more.

In my opinion definitely worth the initial investment, if you're doing any serious kind of work.