[UPDATE] A newer version of DDC has been released to Codeplex
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.
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.
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.
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.
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)
As part of a recent Visual Studio 2005 SP1 announcement, the Corp VP of Microsoft's Developer Division stated that Visual Studio.NET 2003 won't be supported under Windows Vista. Frans Bouma, Paul Wilson, and others have done a good job of raising the level of awareness on the issue. I agree that it's not, you know, a good thing, but I wanted to hear how big a problem it really is.
I kept getting wierd errors in a simple console application which takes a regular expression as an argument. The regular expression kept failing with an "Illegal \ at end of pattern" error. The odd thing was that I was properly escaping the \ as \\.
TimeSnapper is one of my favorite applications. It does one thing and it does it well - it takes a screenshot every few seconds, then lets you browse through your history by date and time. Most people focus on this as a way to simplify filling in time sheets, but I've come to rely on this program as a safety net. This thing has saved my butt countless times:
PalPal's Single Item Purchase links are not technically valid - the query portion of the URL doesn't start with a question mark. This confuses ASP.NET 2.0's Response.Redirect(url) call, which causes the return URL to be malformed. Rather than spend dozens of hours upgrading to the newest version of the store software, I spent 15 minutes with Reflector, WinMerge, and a hex editor to modify the .NET assembly.
SSRS 2005 is pretty slick, but the HTML is just terrible. Reports are displayed in an IFRAME that's deep in nested table land, and the IFRAME's height setting only works in IE. The end result is that reports don't display correctly in Firefox - the IFRAME's height defaults to a few hundred pixels, so you only see the top 2 inches of the report. However, they did the right thing by designating CSS classes for most of the important elements, so we can fix it by adding a min-height setting. I'm sure there are other issues with getting SSRS to display correctly in Firefox, and possibly other answers (let me hear them in the comments below), but this CSS fix at least lets the reports show.