Asking for database documentation in many tech shops will result in blank stares. Other places do see the value of but it forever remains on the to-do list. There are a few commercial products available hoping to help with this;

SchemaToDoc - http://www.schematodoc.com/
SqlSpec - http://www.elsasoft.org/
SQL Doc - http://www.red-gate.com/products/SQL_Doc/index.htm

I’m not convinced of their value especially when important object metadata, or business information,  is missing from your database.

All databases objects should be tagged if appropriate with need-to-know information; Tables should be tagged with some basic information, if that data is licensed and needs annual renewal why not document this inside the database itself?

Columns should have a brief description; it may be obvious to you what the column holds but is it to everyone else? One system I worked with had a TINYINT column called phone_permission with values of 0 or 1. My first guess was that ‘1’ meant you could call the telephone number on the record. Luckily I didn’t run the telesales department so no problem was caused from this misunderstanding.

Adding comments to database tables

In MySQL we can add a comment to a table like so; 
ALTER TABLE TestTable COMMENT 'Data supplied by ACME Corp.';

Table comments can then be viewed by using the SHOW CREATE TABLE syntax.

mysql> SHOW CREATE TABLE TestTable \G
***************************1. row***************************
       Table: TestTable
Create Table: CREATE TABLE `TestTable` (
  `idnm` int(11) default NULL,
  `fullName` varchar(255) default NULL,
  `old_id` int(11) default NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='The data in this table is supplied by ACME Corp.'
1 row in set (0.00 sec)

mysql>

Or by querying INFORMATION_SCHEMA;

-- View table comments for the current database
SELECT TABLE_NAME, TABLE_COMMENT
FROM information_schema.tables
WHERE TABLE_SCHEMA = DATABASE();

For SQL Server it’s a little more complicated. We have to add an extended property to attach a comment onto the table.

EXEC sys.sp_addextendedproperty @name=N'Description',
				@value=N'hello table Customer',
				@level0type=N'SCHEMA',
				@level0name=N'dbo',
				@level1type=N'TABLE',
				@level1name=N'Customer';

Viewing this comment isn’t as easy as MySQL either. Use the below T-SQL to view all table level comments in the current database (dbo schema);

-- Table comments
SELECT objtype, objname, name, value
FROM fn_listextendedproperty
(
	NULL,
	'schema',
	'dbo',
	'table',
	default,
	NULL,
	NULL
);
GO

Viewing Table comments in SSMS

Obviously Microsoft didn’t intend people to hand-code T-SQL to add comments to their databases, but rather use tool support like SSMS. This would probably be rather tedious if you needed to comment a large number of tables. Here’s a suggested method for making this task a little easier. First get the schema and name for all tables in the database you wish to document.

SELECT TABLE_SCHEMA, TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE';

Copy and paste this into Excel, or other spreadsheet program, so you can comment each table easily.

Comment your tables in Excel

When you are finished import this data into SQL Server and run this T-SQL statement against it. N.B. This statement assumes you imported the table comment data into a table called temp_table_comments.

-- Generate T_SQL to add extended properties to all tables
-- in temp_table_comments
SELECT 'EXEC sys.sp_addextendedproperty @name=N''TABLE_COMMENT'',
					@value=N''' + COMMENT + ''',
					@level0type=N''SCHEMA'',
					@level0name=N''' + TABLE_SCHEMA + ''',
					@level1type=N''TABLE'',
					@level1name=N''' + TABLE_NAME + ''';'
FROM temp_table_comments;

Here’s the T-SQL it generates if run against the AdventureWorks database table names. Once you have generated the T-SQL then you’re ready to run it against your database and apply your comments to the tables.

Running the script against the AdventureWorks database

View the table comments added for the Sales schema.

-- Table comments for AdventureWorks
-- Sales schema
SELECT objtype, objname, name, value
FROM fn_listextendedproperty
(
	NULL,
	'schema',
	'Sales',
	'table',
	NULL,
	NULL,
	NULL
)
WHERE [name] = 'TABLE_COMMENT'
GO

Table comments in the Sales schema

It would be fairly easy to take a similar approach for commenting your table columns too. Once your database is nicely documented it’ll be easy to knock up a couple of reports in SSRS and surprise the next person who asks for “the documentation”.