How do you document your databases?

Question

I find that most of my clients are not documenting their databases at all and I find that pretty scary. To introduce some better practice, I would like to know what tools/process people are using.

• How do you document your database? (SQL-Server)
• What tool do you use?
• Documentation Storage Format for database schema/meta-data?
  • Word documents
  • Excel spreadsheet
  • Plain Text
• Documentation process or policies?

I am not talking about reverse engineering / document a existing database, but mainly on the documentation best practices while you develop your system/database.

Answer 1

I have been using extended properties since they are very flexible. Most standard documentation tools can be driven off MS_Description, and then you can use your own with custom-built tools.

See this presentation: #41-Get a Lever and Pick Any Turtle: Lifting with Metadata

And this code: http://code.google.com/p/caderoux/wiki/LeversAndTurtles

Answer 2

Microsoft's Visio Pro (up to Visio 2010) can reverse engineer a database as can CA's ERwin. Visio is the cheaper option, but ERwin is the more detailed, more complete option. Extended properties are nice, if people bother to look at them. You could also use something like Red Gate's SQL Doc to output documentation in HTML format.

I find naming conventions and properly setting up foreign keys lead to an almost self-documenting database. You still should have some external docs for better understanding of purpose.

Answer 3

Try SchemaSpy: http://schemaspy.sourceforge.net/

Answer 4

For SQL Server I'm using extended properties.

With the following PowerShell Script I can generate a Create Table scripts for single table or for all tables in the dbo schema.

The script contains a Create table command, primary keys and indexes. Foreign keys are added as comments. The extended properties of tables and table columns are added as comments. Yes multi line properties are supported.

The script is tuned to my personal coding style.

• no individual collations for single columns.

• currently it requires Sql Server Authentication.

Here is the complete code to turn the extended properties into a good plain old ASCII document (BTW it is valid sql to recreate your tables):

function Get-ScriptForTable
{
    param (
        $server, 
        $dbname,
        $user,
        $password,
        $filter
    )

[System.reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | out-null
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.ConnectionInfo")  | out-null

$conn = new-object "Microsoft.SqlServer.Management.Common.ServerConnection" 
$conn.ServerInstance = $server
$conn.LoginSecure = $false
$conn.Login = $user
$conn.Password = $password
$conn.ConnectAsUser = $false
$srv = New-Object "Microsoft.SqlServer.Management.Smo.Server" $conn

$Scripter = new-object ("Microsoft.SqlServer.Management.Smo.Scripter")
#$Scripter.Options.DriAll = $false
$Scripter.Options.NoCollation = $True
$Scripter.Options.NoFileGroup = $true
$scripter.Options.DriAll = $True
$Scripter.Options.IncludeIfNotExists = $False
$Scripter.Options.ExtendedProperties = $false
$Scripter.Server = $srv

$database = $srv.databases[$dbname]
$obj = $database.tables

$cnt = 1
$obj | % {

    if (! $filter -or  $_.Name -match $filter)
    {
        $lines = @()
        $header = "---------- {0, 3} {1, -30} ----------"  -f $cnt, $_.Name
        Write-Host $header 

        "/* ----------------- {0, 3} {1, -30} -----------------"  -f $cnt, $_.Name
        foreach( $i in $_.ExtendedProperties)
        {
            "{0}: {1}" -f $i.Name, $i.value
        }
        ""
        $colinfo = @{}
        foreach( $i in $_.columns)
        {
            $info = ""
            foreach ($ep in $i.ExtendedProperties)
            {
                if ($ep.value -match "`n")
                {
                    "----- Column: {0}  {1} -----" -f $i.name, $ep.name
                    $ep.value
                }
                else
                {
                    $info += "{0}:{1}  " -f $ep.name, $ep.value
                }
            }
            if ($info)
            {
                $colinfo[$i.name] =  $info
            }
        }
        ""
        "SELECT COUNT(*) FROM {0}" -f $_.Name
        "SELECT * FROM {0} ORDER BY 1" -f $_.Name
        "--------------------- {0, 3} {1, -30} ----------------- */" -f $cnt, $_.Name
        ""
        $raw = $Scripter.Script($_)
        #Write-host $raw
        $cont = 0
        $skip = $false 
        foreach ($line in $raw -split "\r\n")
        {
            if ($cont -gt 0)
            {
                if ($line -match "^\)WITH ")
                {
                    $line = ")"
                }
                $linebuf += ' ' + $line -replace " ASC", ""
                $cont--
                if ($cont -gt 0) { continue }
            }
            elseif ($line -match "^ CONSTRAINT ")
            {
                $cont = 3
                $linebuf = $line
                continue
            }
            elseif ($line -match "^UNIQUE ")
            {
                $cont = 3
                $linebuf = $line
                $skip = $true
                continue
            }
            elseif ($line -match "^ALTER TABLE.*WITH CHECK ")
            {
                $cont = 1
                $linebuf = "-- " + $line
                continue
            }
            elseif ($line -match "^ALTER TABLE.* CHECK ")
            {
                continue
            }
            else
            {
                $linebuf = $line
            }
            if ($linebuf -notmatch "^SET ")
            {
                if ($linebuf -match "^\)WITH ")
                {
                    $lines += ")"
                }
                elseif ($skip)
                {
                    $skip = $false
                }
                elseif ($linebuf -notmatch "^\s*$")
                {
                    $linebuf = $linebuf -replace "\]|\[", ""
                    $comment = $colinfo[($linebuf.Trim() -split " ")[0]]
                    if ($comment) { $comment = ' -- ' + $comment }
                    $lines += $linebuf + $comment
                }
            }
        }
        $lines += "go"
        $lines += ""
        $block = $lines -join "`r`n"
        $block
        $cnt++
        $used = $false
        foreach( $i in $_.Indexes)
        {
            $out = ''
            $raw = $Scripter.Script($i)
            #Write-host $raw
            foreach ($line in $raw -split "\r\n")
            {
                if ($line -match "^\)WITH ")
                {
                    $out += ")"
                }
                elseif ($line -match "^ALTER TABLE.* PRIMARY KEY")
                {
                    break
                }
                elseif ($line -match "^ALTER TABLE.* ADD UNIQUE")
                {
                    $out += $line -replace "\]|\[", "" -replace " NONCLUSTERED", "" 
                }
                elseif ($line -notmatch "^\s*$")
                {
                    $out += $line -replace "\]|\[", "" -replace "^\s*", "" `
                    -replace " ASC,", ", " -replace " ASC$", "" `
                    <#-replace "\bdbo\.\b", "" #> `
                    -replace " NONCLUSTERED", "" 
                }
                $used = $true
            }
            $block = "$out;`r`ngo`r`n"
            $out
        }
        if ($used)
        {
            "go"
        }
    }
} 
}

You can either script thecomplete dbo schema of a given database

Get-ScriptForTable 'localhost'  'MyDB' 'sa' 'toipsecret'  |  Out-File  "C:\temp\Create_commented_tables.sql"

Or filter for a single table

Get-ScriptForTable 'localhost'  'MyDB' 'sa' 'toipsecret' 'OnlyThisTable'

Answer 5

Take a look at SchemaCrawler - it is my free, command-line tool that I designed to do what you are looking for. SchemaCrawler produces a text file with all of database schema objects. This text output is designed to be both human-readable, as well as diff-able against similar output from another server.

In practice, what I have found is that outputting a text file of the database schema is useful, when done as part of the build. This way, you can check the text file into your source code control system, and have a version history of how your schema has evolved over time. SchemaCrawler is designed to automate this too, from the command-line.

Answer 6

If it is ever written, the documentation consists of a word document. A couple of relationship diagrams will be included. Lists of the tables and a brief description of what each table holds and how it relates to other tables. One chapter of the documentation includes the security settings: what permissions does the "user" that the application need?

Generally, in companies I've worked for, database documentation only gets written when the customer is the one who performs audits, which tends to limit its use to financial and government customers.

Disclaimer: far too many developers take the attitude that the code is the documentation, and I've been guilty of it too.

Answer 7

I use extended properties and Red Gates SQL Doc. Works very well!

Answer 8

Funny, I was wondering how other people are doing this as well..

While developing my first big database project, I found that Microsoft SQL Server Management Studio 10.0.1600.22 supports database diagrams which you can export to a word document or other documentation software where you can add as much documentation detail as you want. Just expand the database you connected to on SQL Management Studio and right click on "database diagrams" in the object explorer and select "New Database Diagram" to generate an interactive diagram which will show all the relationships between the different tables. You can even specify which tables you want to include in the diagrams, so that the image does not get unweildly if you are just trying to document it piece by piece. Export the image to any other editing software and comment as much as you want.

I also recommend plenty of /comments/ in the script which generates your database.

Generally it is a lot of work writing down what it is all for, but a good idea for the long term, such as when you or some other poor soul comes back to update your creation a couple of years later! :)

Answer 9

I set the MS_description extended property for all objects and then document the whole database using ApexSQL Doc. I used to create HTML documents earlier, but lately I prefer PDF

Answer 10

I use data modeling tools because they allow me to document important information about the database other than what "fits" in a database. Meta data like privacy/security/sensitivity concerns, stewardship, governance, etc.

That may go beyond what some need in documenting a database, but those things are important to the business and helping them manage their data.

Formal tools also help me in managing data that is stored in more than one database/instance/server. This has never been more true than in our packaged application world.

Answer 11

DB Dictionary Creator

is an open source database documentation tool with decent GUI and export / import options. It uses Extended properties to store the documentation. It'll also generates automatic descriptions for primary key columns and foreign key columns.

Answer 12

For documenting SQL Server, I highly recommend just recently released :

SQL Server & Windows Documentation Using Windows PowerShell written by Kendal Van Dyke

Brief description from the link :

SQL Power Doc is a collection of Windows PowerShell scripts and modules that discover, document, and diagnose SQL Server instances and their underlying Windows OS & machine configurations. SQL Power Doc works with all versions of SQL Server from SQL Server 2000 through 2012, and all versions of Windows Server and consumer Windows Operating Systems from Windows 2000 and Windows XP through Windows Server 2012 and Windows 8. SQL Power Doc is also capable of documenting Windows Azure SQL Databases.

Alternative link: kendalvandyke/sqlpowerdoc github repository, as codeplex is going away.

Answer 13

Indeed, Extended Properties (MS_Description) is the way to go. Having these descriptions readily available as a part of the metadata could be utilized not only by docs generators but also (hopefully one day) by tools that provide "intellisense" for example the excellent Softtree's SQL Assistant http://www.softtreetech.com/isql.htm (last time I checked they didn't) or built in SQL Sever Management Studio's Intellisense (since sql2008)

I also believe it should be easy for devs and DBA's to add these notes because as Tangurena and Nick Chammas correctly pointed out - devs are very reluctant to keep the docs updated and hate duplicate work - which is fair enough especially for a person who was taught to optimize things during their entire professional life. So unless it's really easy to update docs in one place close to source code - this isn't gonna work.

At some point I searched the web and didn't find a solution to this so I wrote LiveDoco (not free, sorry) in attempt to make it easy. More info here if interested: http://www.livedoco.com/why-livedoco (Website / LiveDoco is dead as of 5/4/2020).

Answer 14

We use Dataedo to create data dictionary, document stored procedures and functions. We paste ERDs created in Visio. All documentation is stored in Dataedo metadata repository (formatted text) and we export it to HTML for internal use or export to PDF for printed document.

We assign each object to a module and assign each module to a person. Dataedo comes with documentation status reporting so we can tell if there’s a new column or table that needs to be documented.

Answer 15

You can use regular ---prefixed comments in the .sql file.

Benefits include that the documentation is with the code for the database schema and you can easily commit it to a version control system such as Git.

Example:

-- Table to store details about people.
-- See also: The customer table.
-- Note: Keep this data safe!
-- Todo: Add a email column.
CREATE TABLE Persons ( -- People in the registry
    PersonID int,
    LastName varchar(255), -- The person's last name
    FirstName varchar(255), -- The person's first name
    Address varchar(255), -- Address of residence
    City varchar(255) -- City of residence
);

Maybe you could use XML too.

-- <summary>
-- Table to store details about people.
-- </summary>
-- <column name="PersonID">The id column.</column>
-- <column name="LastName">The person's last name.</column>
-- <column name="FirstName">The person's first name.</column>
-- <column name="Address">Address of residence.</column>
-- <column name="City">City of residence.</column>
CREATE TABLE Persons (
    PersonID int,
    LastName varchar(255),
    FirstName varchar(255),
    Address varchar(255),
    City varchar(255)
);

You could also use some syntax with resemblance of jsDoc/phpDoc.

-- Table to store details about people.
-- @column {int} PersonID - The id column.
-- @column {varchar} LastName - The person's last name.
-- @column {varchar} FirstName - The person's first name.
-- @column {varchar} Address - Address of residence.
-- @column {varchar} City - City of residence.
-- @see {@link https://example.com/|Example}
-- @author Jane Smith <[email protected]>
-- @copyright Acme 2018
-- @license BSD-2-Clause
-- @todo Add a column for email address.
-- @since 1.0.1
-- @version 1.2.3
CREATE TABLE Persons (
    PersonID int,
    LastName varchar(255),
    FirstName varchar(255),
    Address varchar(255),
    City varchar(255)
);

Or you could use MarkDown syntax.

-- # Persons
-- Table to store details about **people**.
-- * `PersonID` - The id column.
-- * `LastName` - The person's _last_ name.
-- * `FirstName` - The person's _first_ name.
-- * `Address` - Address of residence.
-- * `City` - City of residence.
--
-- [I'm an inline-style link](https://www.example.com/)
--
-- | PersonID | LastName | FirstName | Address | City |
-- | ---------| -------- | --------- | ------- | ---- |
-- | 1        | Smith    | Jane      | N/A     | N/A  |
CREATE TABLE Persons (
    PersonID int,
    LastName varchar(255),
    FirstName varchar(255),
    Address varchar(255),
    City varchar(255)
);

Answer 16

After having tried many of the free & paid solutions here over the years, I've created my own open source solution in pure T-SQL that outputs documentation in Github Flavor Markdown, sp_doc as part of a larger suite of free scripts called DBA MultiTool.

Doing it via T-SQL reduces the complexity of relying on a third party application, PowerShell module/library, etc. and leverages the built-in power of Extended Properties to make the database self describing. It can be easily hooked into automated deployment workflows to produce an always up-to-date view of the database.

Databases can be complex, disastrous things. Not every database admin, developer, or analyst has the time to learn the ins and outs of a database in order to just do their work. To make things worse, few products and fewer free options exist to help present databases in a human readable format.

sp_doc's goal is to generate on the fly database documentation in Github Flavor Markdown (GFM). This means you now have a free and extensible self-documenting database!

Update: It can now also be installed via dbatools' Install-DbaMultiTool command as well.

Answer 17

ERD Diagrams (Database Diagrams) were always the most useful ones for my team

But there is rule to write "Decription" in Properties of each table and column which we create.

Then we use a software name is Enterprise Architect to document Tables with all Indexes , Foreign Keys And Columns with Type and Description.

Answer 18

For MySQL in particular, we always use MySQL Workbench. We create our database designs in the designer, then export it as a runnable SQL script. Applying all the changes in the design and then running the generated script ensures that the design and the actual database are perfectly in sync with each other, and that the documentation will not become outdated as easily.