Data Dictionary Creator

[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 helpsyou document SQL Server databases. It stores all the information inExtended Properties, so it's easier to keep the documentation in syncwith the database as it changes. You could do a lot of this throughSSMS (SQL Server Management Studio), but

1. It's a lot more work, which means it's a lot less likely to be kept updated.
2. The SSMS approach only gives you one column (Description); DDCgives you as many as you want. That's probably one of the best featuresDDC adds - it lets you tailor your documentation to your database andbusiness.
   
3. SSMS requires you to write your export via tricksy SQL; DDC exports to WordML, Excel, HTML, and XML.

   Download DataDictionaryCreator from CodePlex.
   <script type="text/javascript"><!--google_ad_client = "pub-3555979289815451";google_ad_slot = "0437120238";google_ad_width = 468;google_ad_height = 60;//--></script><script src="http://pagead2.googlesyndication.com/pagead/show_ads.js" type="text/javascript"></script>
   

How to use it

Connect to database

Enter a connection string (you know the drill). You canalternatively double click the connection string textbox for thestandard data connection (DataLink) dialog. The connection string boxturns 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 ofproperties. In the example above, I've included a Source and Auditcolumn. You can change it at any time, so don't worry about it too muchif you're not sure right now.

Auto Fill the key column descriptions

Click the "Set descriptions for all keys" button to automaticallypre-fill descriptions for all primary and foreign key columns. This isoptional, but it saves some work and helps you to see some progressquickly.

Fill in the descriptions

Go to the Edit tab and do the actual work. Nope, there's no GhostDocaction here - you have to fill in the information. It's pretty easy todo, though - select a table, fill in the table description, and fill insome documentation fields. All changes are saved as you work (when youleave each grid cell). Then pick another table and continue. You can doas much or as little as you like; you can make additional changeswhenever 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 selectHTML, Excel, WordML, or XML. All exports go first to a native XMLformat, then through an XSL transform, so you can customize the exportby modifying the XSLT files or use them as the template for a transformto any other format you'd like.

Repeat

The best part of this is that it's not a one time thing. Anyone whoworks on the database can continue to update the documentation as theygo.

How would you do this in SSMS?

Oh, you're still stuck on that, huh? Okay, if you want to do thisthrough SQL Server Management Studio (SSMS), the best way is throughthe Database Diagram interface. Editing through the Modify Table screenis really inefficient since you can only update the description for onecolumn at a time. The Database Diagram screen has a custom view whichallows you to add the description column so you can update all columndescriptions for a table at a time.

Here's how to set that up

1. Create a table database diagram
2. Switch the table view to "Custom View" - right-click the table name and select custom view
3. 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) providedwith SQL Server 2005. They make dealing with database metadata reallyeasy, so I could concentrate on the harder stuff, like setting upcontrol containers and persisting user settings. ;-) The source code isincluded under BSD license, so feel free to take a look and reuseanything you find useful. Please submit any changes you'd like me toinclude in the next release. The easiest thing to modify is the exportXSLT 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)
  

 

Published Thursday, September 28, 2006 10:03 AM by Jon Galloway Filed under: Tools, SQL, SQL Server