Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question
4  
I would be interested for PostgreSQL too. –  Spredzy Jan 11 '11 at 9:58
    
I'm curious to see what "database documentation" even looks like, aside from the obvious schema diagram. I have trouble envisioning what you would document and how. –  TML Jan 11 '11 at 23:31
3  
@TML: How database documentation should look like, you can read in Applied Mathematics for Database Professionals by Lex de Haan and Toon Koppelaars (by the way a very nice book with a misleading title), but I doubt that (m)any people do it this way. –  j.p. Jan 18 '11 at 15:32
    
@jug: if I decide not to buy the book, could you post an answer with an example of the documentation format? –  John Saunders Jan 20 '11 at 3:34
    
@John Saunders: If you google for "Specifying the Example Database Design" (with quotes!), you should get (probably depending on the country you are) quite at the top (here it was 2nd) a link to google books were you can see their recommendation. –  j.p. Jan 20 '11 at 14:13

14 Answers 14

I have been using extended properties. 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: http://www.sqllunch.com/Lunches.aspx?Month=November%202010#

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

share|improve this answer
2  
+1 for extended properties –  Larry Coleman Jan 11 '11 at 13:46
1  
+1 for the properties :). –  Marian Jan 18 '11 at 13:03
    
Also a +1 for extended properties. I use them for all sorts of management related tasks :D –  AndrewSQL Jan 19 '11 at 1:30
    
+1 for the LeversAndTurtles presentation for making extended properties more usable. –  Jeff Mar 15 '11 at 18:12
1  
You can change something and forget to change your extended properties accordingly, rendering them incorrect. Can you automatically detect such discrepancies? –  AlexKuznetsov Mar 25 '13 at 0:43

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 one that performs audits, which tends to limit it 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.

share|improve this answer
7  
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. –  Nick Chammas Aug 19 '11 at 20:24

Microsoft's Visio Pro 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 setup foreign keys lead to an almost self documenting database. You still should have some external docs for better understanding of purpose.

share|improve this answer
1  
I also use Visio to generate nice looking diagrams for the majority of my documentation. –  SchwartzE Jan 11 '11 at 21:13
1  
+1. And if you use the [not update for a while] "Visio for Enterprise Architects" you can forward engineer databases and also generate rtf/word doc reports from Visio db models. Unfortunately, the notes/documentation entered for tables/columns in Visio models are not exported into SQL Server's extended properties [from that version of Visio] when generating SQL-DDL but it is still great for generating diagrams and data model dictionaries. –  KristoferA Jan 19 '11 at 6:03
    
@Kristofer: not only has VEA not been updated for a while, it never will be updated. Note that it's not even based on the current version of Visio. Microsoft dropped it as a software engineering tool some time ago. –  John Saunders Jan 20 '11 at 3:26
1  
@John Saunders I know. However, it is still a decent tool so although it is 'dead' it can still be used to generate neat ER-diagrams and accompanying documentation. Kind of sad that they dropped it before they had a replacement. –  KristoferA Jan 26 '11 at 3:39

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.

share|improve this answer

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

share|improve this answer

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 Create table, 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 Authenication.

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'
share|improve this answer
    
FYI, you can use RedGate SQL Doc to update your extended properties as well as include them in it's documentation for your database. Great combo! –  Jeff Jan 4 '12 at 22:35

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

share|improve this answer

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 foriegn key columns.

share|improve this answer
    
requires .NET Framework 4.0 and only works with SQL Server and SQL Express –  kevinsky Oct 7 '12 at 15:55
    
+1 - This worked well to get a full description of all the tables. –  John M Nov 8 '13 at 16:43

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.

share|improve this answer

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

share|improve this answer

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

share|improve this answer

You could also take a look at wsSqlSrvDoc. It's a nice little tool that works with SQL Server extended properties and creates a MS Word document.

The print-out of all column properties (with foreign key relations) works out of the box. For further descriptions on each field you have to set up extended properties of those columns in SQL Server Management Studio.

It's not free but quite affordable. If you just need to create a documentation for a "not work in progress" DB that's more or less finished than it would be enough to use the free trial.

Tool Website

share|improve this answer
1  
Updated address for tool: wolfsys.net/free-downloads –  John M Nov 8 '13 at 16:37

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.

share|improve this answer

protected by Jack Douglas Dec 29 '13 at 16:56

Thank you for your interest in this question. Because it has attracted low-quality answers, posting an answer now requires 10 reputation on this site.

Would you like to answer one of these unanswered questions instead?

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