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

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.

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.

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

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”.