Around the project that i'm focusing on I've got a handful of databases. Each table and every column within the database includes a description set (being an extended property in SQL 2005). As part of the documentation visiting the client we have to create a data dictionary showing all the tables and posts together with an accumulation of meta data (data-type, optionality, constraints).

Is anybody utilizing a tool to instantly create this type of document? If that's the case, which tools would you use? I have tried personally Data Dictionary Creator that is awesome however it does not appear to complete data types or optionality (unless of course you need to include custom fields and fill them in yourself).

They are all very SQL Server 2000+ specific

Free / Free

SQL XML Documentation

DBdoc: automated CHM documentation for any SQL Server 2000 database

SQLDoc Sharp

Commercial

Apex SQL Doc

RedGate SQL Doc

Visio (Just for diagramming ERD's)

A few of these tools are GUI driven which forbids their usage in continuous integration style development. I hear good stuff abouts Red-colored Gate and also have used Apex SQL once before with success.

The one thing that bugs me happens when it involves interpretation comments etc in databases. There seems to become no agreed standard for SQL documentation. I'd love a typical such as the XML comments for C#/VB code whereby managing a process like sandcastle across your code/comments creates helpful documentation. A few of the commercial suppliers get their own proprietary approach.

Outdoors source tool Schema Spy is effective. Sample output can be obtained here. In case your dealing with oracle databases, Oracle includes a free SQL Developer Tool that creates schema documentation much like the creation of schema spy.

Toad Data Modeller from Mission Software performs this and it is quite reasonably listed.

In the Schema Spy documentation it supports multiple databases (see below). Additionally, it supports data types, constraints and maps associations as much as two levels of separation.

The Samples are very well worth searching at.

Schema Spy Supported Databases

IBM DB2 using the 'App' Driver,IBM DB2 using the 'Net' Driver, Firebird, HSQLDB Server, Microsoft SQL Server,MySQL, Oracle with OCI8 Driver, Oracle with Thin Driver, PostgreSQL, Sybase Server with JDBC3 Driver, Sybase Server with JDBC2 Driver, DB2 UDB Type 4 Driver

Red Gate SQL Doc is excellent. It can produce a Word document or number of HTML pages that customers can navigate.

If you are using Firebird or InterBase, there is a tool known as IBDesc that does a fantastic job.