This article presents 8 different types of tools you can use to create a documentation of your existing databases.

In short, they are:

Let's have a closer look:

1. Word processors

When you want to create a printable document, word processors (like MS Word or LibreOffice Writer) sound like an obvious choice.

Benefits

You are most likely already using it

Good formatting, branding and printing capabilities

Disadvantages

It's a nightmare to prepare

to prepare It's an even worse nightmare to maintain

2. Spreadsheets

A slightly better choice for a tool would be a spreadsheet (like MS Excel or LibreOffice Calc). It makes navigating, searching and filtering data more convenient. It also feels more natural since documentation usually has tabular structure.

Benefits

You are most likely already using it

Easy to search and filter metadata

Disadvantages

Poor printing capabilities

It's difficult to prepare

It's a nightmare to maintain

3. Database development and management tools

Standard database consoles like SQL Server Management Studio (SQL Server), Oracle SQL Developer (Oracle) or MySQL Workbench (MySQL) support some basic database schema and model documentation and generation.

Those features might include:

Commenting data elements (tables, columns, views etc.)

Generating HTML or PDF documentation

Reverse engineering database schema to ER Diagrams

Benefits

Most of them are free or shipped with the database engine

DBAs, developers and architects already use database management tools

Disadvantages

No global documentation for both - objects descriptions and diagrams

Little or no editing and authoring capabilities (only basic annotation features)

No repository for diagrams

No ability to generate integrated documentation consisting of diagrams and detailed data dictionary

Tool examples

SQL Server: SQL Server Management Studio

Oracle: Oracle SQL Developer

MySQL: MySQL Workbench

Database documentation generated with Oracle SQL Developer

Diagram generated with SQL Server Management Studio

Diagram generated with MySQL Workbench

4. Documentation generators (read only)

There is a number of dedicated tools whose sole purpose is to generate documentation from your database schema. This category of tools doesn't have any editing capabilities and can only generate a documentation from metadata extracted from the database.

Benefits

Easy to use

Better formatting than standard database tools

Disadvantages

No editing capabilities, separate tool is required for this (e.g. database management tool)

Annotating capabilities are limited by database platform (you can't provide rich text descriptions or describe elements if DBMS doesn't support that)

Tool examples

dbForge Documenter for SQL Server

Elasoft SqlSpec

Spectral Core Documenter

Documentation generated with dbForge Documenter for SQL Server

5. Documentation tools (read/write)

There is a number of tools that enable you to do both functions of documenting:

describe tables and columns (data dictionary), or other database objects and generate convenient documents for sharing.

This category is different from the previous in editing capabilities which is a huge difference as providing the descriptions is a key documentation activity. This is where value added is being created. Document generation just makes it easier to access and share, while providing descriptions of data structures gets the knowledge out of the peoples heads and saves it for later reference.

Those tools mostly store metadata (descriptions) in the database itself - in Extended Properties in the case of SQL Server or comments in the case of MySQL and Oracle.

Benefits

Ability to describe/annotate data elements (tables, columns etc)

Disadvantages

Metadata scope limited by database capabilities (you can't add more information than the engine enables)

Tool examples

Redgate SQL Doc

ApexSQL Doc

Elasoft SqlSpec

Redgate SQL Doc description editor and documentation generator

6. Advanced documentation tools (read/write + metadata + authoring)

There are tools that go beyond extended property/comment editors and documentation generators - let's call them metadata repositories. Those tools keep data in a separate repository which enables them to gather and store much more metadata and makes them independent on the database platform. They provide more functionalities: advanced authoring, more metadata, database diagrams.

Benefits

Global repository

Rich metadata

Better authoring capabilities

Support for multiple database engines

Disadvantages

Slightly more difficult to set up and learn

Tool examples

Documentation generated with Dataedo (free trial)

Live sample

7. Data Modeling tools

There is a large category of tools which are designed specifically for logical and physical engine-independent data modeling. They provide forward and reverse engineering functionalities. They are good for creating ER Diagrams, but much less for describing data elements (creating Data Dictionaries). Even worse at the maintenance of the documentation.

Benefits

Global repository

Rich metadata

Better authoring capabilities

Support more than one

Support for multiple database engines

Disadvantages

Very complicated and overloaded with features

Designed for forward engineering purposes, not really ideal for reverse engineering

Mostly non-convenient column descriptions editing (Data Dictionary)

Clunky default export documents

Exporting sometimes requires programming/customizations

Tool examples

Erwin

SAP PowerDesigner

Idera ER/Studio Data Architect

8. Diagramming tools

If you just want to create database diagrams (it's not a full documentation of the database) you can use generic diagramming tool.

Benefits

Good diagramming and visual capabilities

Disadvantages

No support for Data Dictionary (a description of each data element)

Most of these tools don't support connections to database

Even if they support connection to a database, it is not easy to maintain such models when database schema changes

No support for other database elements - stored procedures, functions, triggers etc.

Tool examples

MS Visio

Gliffy (online)

LucidChart (online)

Conclusion

I hope this is a convenient overview of the tools available if you want to document your existing databases.