Database Documentation Made Easy

Data Dictionary Creator is a simple application which helps you document SQL Server databases. It stores all the information in Extended Properties, so it's easier to keep the documentation in sync with the database as it changes.  The tool is open source and available as an executable and source code on codeplex.  The first code release was November 2006 with over 40 releases and patches up until today.  Authored in C#.Net.

Database Dictionary Creator

I could go through a step-by-step, but there is already a great walkthrough online.

The html grouped option was great for me - it even creates the anchor tags to jump from the table list to the table details. 

The only step remaining in my mind was to link the database diagram to the table details by exporting a diagram from sql server and adding an image map.  Since those steps are custom, I'll document them here:

Exporting a SQL Server Relationship Diagram

  1. Right-click in the diagram pane
  2. Choose "Copy Diagram to Clipboard"
  3. Paste in an image management destination program and save as image

Creating an Imagemap

  1. Open an online imagemap creator such as http://www.maschek.hu/imagemap/imgmap
  2. Upload an image or provide a url to an image and click "accept"
  3. Draw a box around each table in the diagram.  Add hypertext references (href) and alternative text (alt) attributes for each hyperlink.  Href's should link to the anchors in the format "#dbo.{tablename}", alt tags are displayed on hover, "View {tablename}" is probably appropriate.
  4. When all tables have been outlined and linked, click code and copy the text

Putting it all together

  1. Open the html produced by the Database Documention Creator in export grouped mode
  2. Just inside the body tag (after <body>), add a reference to the image you created (hint: it must be accessible to the html you are viewing - it is easiest to place it in the same directory).  An html image is referenced like the following <img src="imagename.format" alt="Alternative Text to Display" />
  3. After your image paste the code you copied.  This is the imagemap it must be using the same copy of the image that your used to create the imagemap.  If the image has been resized or modified the coordinates will no longer match.
  4. Link the imagemap to the image by copying the map id from the map code to the image tag like the following: <img usemap="#map id value" src="imagename.format" alt="Alternative Text to Display" />.  Notice I have added a pound symbol before the id attribute.
  5. Save your html and view it in a browser.  The product should resemble http://fishandgame.idaho.gov/ifwis/core/home/model.

How this could be better

The source code for both applications I have used are available online.  The Database Dictionary Creator is available for free and the Imagemap Creation Tool for a modest donation.  The whole process might be slightly smoother if we write some code to wrap together the imagemap creation and publishing to the web.  I'm thinking we use the existing Database Dictionary Creator to export the grouped html, then have an upload page which accepts the created html and an image.  The user then adds an imagemap and the script cleans up the html, puts it all together into one page and publishes the product.

SQL Server Documentation

Thanks for sharing about this great documentation tool! I have used a similar commercial tool (SQL Doc by Red Gate Software). I have blogged it in my blog . It looks like Data Dictionary Creator is a good alternative.