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 tablesIn 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();
EXEC sys.sp_addextendedproperty @name=N'Description', @value=N'hello table Customer', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'Customer';
-- 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”.