244

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.

0

18 Answers 18

84

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

2
  • 4
    You can change something and forget to change your extended properties accordingly, rendering them incorrect. Can you automatically detect such discrepancies?
    – A-K
    Mar 25, 2013 at 0:43
  • 3
    At the very least, one could query the database schema (sys.tables / sys.columns) and left join to its extended properties (sys.extended_properties) to identify undocumented fields, then turn that script into a test to run when deploying.
    – Micah
    Feb 18, 2016 at 21:11
62

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.

2
  • 2
    What a simple schema is often missing (even in a well-named and foreign-keyed database) is descriptions of the fields. It's unusual in my experience to have all fields simple enough to fit into the column name.
    – StockB
    May 17, 2016 at 19:54
  • To add emphasis to this, tools like Visio or ApexSQL's Model rely on having all foreign key relationships defined. If they are not defined, then the tools set the tables off as their own independent objects. So if you have inherited a database with a lot of undefined foreign keys, the free\cheap tools won't be too much help.
    – BCM
    Dec 10, 2020 at 21:07
29

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

1
27

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'
0
22

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.

4
  • This looks like a very good tool but it is SOOO challenging and difficult to find simple examples: a webpage. Why force people to download samples from GitHub just to see an example. Should have done better in that department. Even a WiKi Sep 7, 2021 at 12:40
  • @TheRealChx101 - please see katacoda.com/schemacrawler to get started with hands-on within a browser - no downloads Sep 8, 2021 at 1:45
  • Seriously? I have to sign up just to access examples? And I downloaded the terminal version (.deb) but I do not even know how to launch it. I can see it in my applications but clicking on it does not do anything. Doesn't even show up on the terminal. Sep 8, 2021 at 19:58
  • @TheRealChx101 - You can always get a paid version with full support - schemacrawler.com/consulting.html :-) Anyway, the .deb version is a third-party release, and is not supported by the SchemaCrawler team. Welcome to the world of open source. Sep 8, 2021 at 21:09
20

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.

1
  • 12
    A big problem I find with documentation that is not bound closely to the code (e.g. a separate Word document, as opposed to an auto-generated schema diagram + well-named database objects) is that this documentation is guaranteed to be flat out wrong as time passes. The reason is simple: a separate document effectively duplicates information. Without an automated way to keep it in sync with the source, it will become obsolete very quickly. Compare this to a tool that generates a schema diagram live from the database and pulls the appropriate comments from within the code. Aug 19, 2011 at 20:24
17

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

14

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! :)

1
  • 3
    I do not use the SQL Server diagrams, as the foreign key constraints are just connected somewhere to the tables, like it is done in ER-diagrams. I prefer to have the connectors connecting the primary and foreign key fields. Jul 15, 2016 at 11:43
14

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

12

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.

10

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.

2
  • 1
    requires .NET Framework 4.0 and only works with SQL Server and SQL Express
    – kevinskio
    Oct 7, 2012 at 15:55
  • 2
    Not maintained since 2010 Jul 8, 2020 at 16:13
10

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.

8

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).

5

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.

2

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)
);
2

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.

1

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.

enter image description here

-1

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.

Not the answer you're looking for? Browse other questions tagged or ask your own question.