Which tools do people use to create Data Dictionaries?

Question

On the project that I am working on I have a couple of databases. Each table and each column in the database has a description set (as an extended property in SQL 2005). As a part of the documentation going to the client we need to produce a data dictionary showing all of the tables and columns along with a collection of meta data (data-type, optionality, constraints).

Is anyone using a tool to automatically create this kind of document? If so, which tools do you use? I have used Data Dictionary Creator which is awesome but it doesn't seem to do data types or optionality (unless you want to add in custom fields and fill them in yourself).

Answer 1

Toad Data Modeller from Quest Software does this and is quite reasonably priced.

Answer 2

The open source tool Schema Spy works well. Sample output is available here. If your working with oracle databases, Oracle has a free SQL Developer Tool that generates schema documentation very similar to the output of schema spy.

Answer 3

From the Schema Spy documentation it supports multiple databases (see below). It also supports data types, constraints and maps relationships up to two degrees of separation.

The Samples are well worth looking at.

Schema Spy Supported Databases

IBM DB2 with the 'App' Driver,IBM DB2 with 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

Answer 4

If you use Firebird or InterBase, there's a tool called IBDesc that does a great job.

Answer 5

Red Gate SQL Doc is very good. It can create a Word document or series of HTML pages that users can navigate.

Answer 6

These are all very SQL Server 2000+ specific

Free / Open source

SQL XML Documentation

DBdoc: automated CHM documentation for a SQL Server 2000 database

SQLDoc Sharp

Commercial

Apex SQL Doc

RedGate SQL Doc

Visio (Only for diagramming ERD's)

Some of these tools are GUI driven which prohibits their usage in continuous integration style development. I hear good things abouts Red Gate and have used Apex SQL once before with success.

The thing that bugs me is when it comes to interpreting comments etc in databases. There appears to be no agreed standard for SQL documentation. I'd love a standard like the XML comments for C#/VB code whereby running a process like sandcastle across your code/comments generates useful documentation. Some of the commercial vendors have their own proprietary approach.

Answer 7

Has anyone had experience with DBScribe? I downloaded the trial and looked at the samples. It seems to be pretty good.

Answer 8

SchemaCrawler for Microsoft SQL Server is a free and open source tool to create database documentation, with complete details of the schema metadata. It can generate HTML as well as text. You can create database diagrams also. The advantage is that the output format is designed to "diff"-able, allowing you to keep track of schema changes easily.