[Tool] Data Dictionary Creator - Rapid database documentation
[UPDATE] A newer version of DDC has been released to Codeplex
Summary
Data Dictionary Creator (DDC) 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. You could do a lot of this through SSMS (SQL Server Management Studio), but
- It's a lot more work, which means it's a lot less likely to be kept updated.
- The SSMS approach only gives you one column (Description); DDC gives you as many as you want. That's probably one of the best features DDC adds - it lets you tailor your documentation to your database and business.
- SSMS requires you to write your export via tricksy SQL; DDC exports to WordML, Excel, HTML, and XML.
Download DataDictionaryCreator from CodePlex.
How to use it
Connect to database
Enter a connection string (you know the drill). You can alternatively double click the connection string textbox for the standard data connection (DataLink) dialog. The connection string box turns light blue (as shown below) when you're connected.
Set up any Additional Properties you want to track
The Additional Properties box takes a comma delimited list of properties. In the example above, I've included a Source and Audit column. You can change it at any time, so don't worry about it too much if you're not sure right now.
Auto Fill the key column descriptions
Click the "Set descriptions for all keys" button to automatically pre-fill descriptions for all primary and foreign key columns. This is optional, but it saves some work and helps you to see some progress quickly.
Fill in the descriptions
Go to the Edit tab and do the actual work. Nope, there's no GhostDoc action here - you have to fill in the information. It's pretty easy to do, though - select a table, fill in the table description, and fill in some documentation fields. All changes are saved as you work (when you leave each grid cell). Then pick another table and continue. You can do as much or as little as you like; you can make additional changes whenever you want.
Notice that the Audit and Source columns have been added based on my previous settings.
Export
Go back to the Setup tab and click the Export button. You can select HTML, Excel, WordML, or XML. All exports go first to a native XML format, then through an XSL transform, so you can customize the export by modifying the XSLT files or use them as the template for a transform to any other format you'd like.
Repeat
The best part of this is that it's not a one time thing. Anyone who works on the database can continue to update the documentation as they go.
How would you do this in SSMS?
Oh, you're still stuck on that, huh? Okay, if you want to do this through SQL Server Management Studio (SSMS), the best way is through the Database Diagram interface. Editing through the Modify Table screen is really inefficient since you can only update the description for one column at a time. The Database Diagram screen has a custom view which allows you to add the description column so you can update all column descriptions for a table at a time.
Here's how to set that up
- Create a table database diagram
- Switch the table view to "Custom View" - right-click the table name and select custom view
- Customize the view - right-click the table, select "Modify Custom", and add the Description as shown in the screenshot below
That helps a bit, but you need to go to each table to make updates. If you want to export it, take a look at Raymond Lewallen's SQL script to generate a data dictionary table.
Tech Stuff
I made heavy use of the SQL Server Management Objects (SMO) provided with SQL Server 2005. They make dealing with database metadata really easy, so I could concentrate on the harder stuff, like setting up control containers and persisting user settings. ;-) The source code is included under BSD license, so feel free to take a look and reuse anything you find useful. Please submit any changes you'd like me to include in the next release. The easiest thing to modify is the export XSLT files, which are pretty plain right now.
Minor Update 9/29/06
Version 1.1.0 adds:
- T-SQL export to allow copying documentation between database instances (thanks, Tyler!)
- Loads previously used additional properties from database on connection (thanks for the suggestion, Phil, although you made me do all the work...)
- User interface inhancements (thanks, Phil)
- Bug fixes (I wrote 'em, I fixed 'em)