Documenting databases is fun again!

No, not really. But a least it's not a complete waste of time. Unlike those data documents you've written in the past, this one lives with your database (in SQL Server Extended Properties) so it stays up to date. Follow along and we'll show you how easy it is to create useful documentation for that good old Northwind database...

Make a connection

The first step is to connect to a database. You can enter a connection string or click on the "..." button to view the standard data connection dialog.
DDC_ConnnectTab.png

Hit the ground running - get advanced!

You can breeze right by the next tab (Advanced Settings) if you'd like, but it's got some neat gizmos that could save you some time. The two big features:
  • Auto-Fill - this looks at you schema and adds descriptions for primary and foreign keys. You can customize the text these use.
  • Additional Properties - By default, you're only filling in a simple "Description" tab. If you'd like add other columns to your documentation, you're going to want to look at Additional Properties.
DDC_AdvancedTab.png

Document that DB

This is where the rubber meets the road. You're going to need to fill this out, but we've made it a lot easier for you:
  1. The user interface shows you column names and datatypes to help you remember what all those columns are used for.
  2. The datagrid lets you move around pretty quickly
  3. Your work is saved immediately, so you can work on it as time permits. Your edits are saved with the database.
DDC_DocumentDbTab.png

Show off!

Now that you've documented your database, you can export it to share with others. There are two ways to share it:
  1. Documentation formats (Word, Excel, HTML)
  2. Importable formats (T-SQL, XML) - these let other users import your documentation into their copy of the database
DDC_ExportTab.png

Here's what the export looks like in Excel. Note that the Table and Column documentation are on separate worksheets. Feel free to pick your favorite AutoFormat to pretty this up.
DDC_Export_Excel.png

Here's the HTML export:
DDC_Export_HTML.png

The next two formats are importable. The T-SQL export can be executed via Query Analyzer / SSMS, or it can be loaded by DDC.
DDC_Export_TSQL.png

Here's the XML format. The only reasons you'd want to use this are to import into another copy of the database via DDC, or to export to another format. All file exports actually start with XML and go through an XSL transform. You can check out the XSL folder and modify any of these, or create your own.
DDC_Export_XML.png

Copy that documentation with Import

You can use the Import tab to load previously exported documentation. Browser for a T-SQL or XML export from DDC. We do a few checks to make sure the file was created by DDC, then load the documentation into the currently connected database.
DDC_ImportTab.png

Last edited Nov 15, 2006 at 8:35 AM by jongalloway, version 6

Comments

airyce Apr 9, 2010 at 2:43 AM 
yeah it looks great but it doesn't work on us! we have a SQL 2005 here so after we had used it this is the error :

every object we clicked it shows that error (>.>)
===============================================
See the end of this message for details on invoking
just-in-time (JIT) debugging instead of this dialog box.

************** Exception Text **************
System.IO.FileNotFoundException: Could not load file or assembly 'Microsoft.SqlServer.ConnectionInfo, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91' or one of its dependencies. The system cannot find the file specified.
File name: 'Microsoft.SqlServer.ConnectionInfo, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91'
at DataDictionaryCreator.Main.DocumentationSetup(String sqlConnectionString)
at DataDictionaryCreator.Main.MainForm_Load(Object sender, EventArgs e)
at System.Windows.Forms.Form.OnLoad(EventArgs e)
at System.Windows.Forms.Form.OnCreateControl()
at System.Windows.Forms.Control.CreateControl(Boolean fIgnoreVisible)
at System.Windows.Forms.Control.CreateControl()
at System.Windows.Forms.Control.WmShowWindow(Message& m)
at System.Windows.Forms.Control.WndProc(Message& m)
at System.Windows.Forms.ScrollableControl.WndProc(Message& m)
at System.Windows.Forms.ContainerControl.WndProc(Message& m)
at System.Windows.Forms.Form.WmShowWindow(Message& m)
at System.Windows.Forms.Form.WndProc(Message& m)
at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m)
at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)
at System.Windows.Forms.NativeWindow.Callback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)

WRN: Assembly binding logging is turned OFF.
To enable assembly bind failure logging, set the registry value [HKLM\Software\Microsoft\Fusion!EnableLog] (DWORD) to 1.
Note: There is some performance penalty associated with assembly bind failure logging.
To turn this feature off, remove the registry value [HKLM\Software\Microsoft\Fusion!EnableLog].



************** Loaded Assemblies **************
mscorlib
Assembly Version: 2.0.0.0
Win32 Version: 2.0.50727.3603 (GDR.050727-3600)
CodeBase: file:///c:/WINDOWS/Microsoft.NET/Framework/v2.0.50727/mscorlib.dll
----------------------------------------
DataDictionaryCreator
Assembly Version: 1.2.0.0
Win32 Version: 1.2.0.0
CodeBase: file:///E:/Program%20Files/DataDictionaryCreator/DataDictionaryCreator.exe
----------------------------------------
System.Windows.Forms
Assembly Version: 2.0.0.0
Win32 Version: 2.0.50727.3053 (netfxsp.050727-3000)
CodeBase: file:///C:/WINDOWS/assembly/GAC_MSIL/System.Windows.Forms/2.0.0.0__b77a5c561934e089/System.Windows.Forms.dll
----------------------------------------
System
Assembly Version: 2.0.0.0
Win32 Version: 2.0.50727.3053 (netfxsp.050727-3000)
CodeBase: file:///C:/WINDOWS/assembly/GAC_MSIL/System/2.0.0.0__b77a5c561934e089/System.dll
----------------------------------------
System.Drawing
Assembly Version: 2.0.0.0
Win32 Version: 2.0.50727.3053 (netfxsp.050727-3000)
CodeBase: file:///C:/WINDOWS/assembly/GAC_MSIL/System.Drawing/2.0.0.0__b03f5f7f11d50a3a/System.Drawing.dll
----------------------------------------
System.Configuration
Assembly Version: 2.0.0.0
Win32 Version: 2.0.50727.3053 (netfxsp.050727-3000)
CodeBase: file:///C:/WINDOWS/assembly/GAC_MSIL/System.Configuration/2.0.0.0__b03f5f7f11d50a3a/System.Configuration.dll
----------------------------------------
System.Xml
Assembly Version: 2.0.0.0
Win32 Version: 2.0.50727.3082 (QFE.050727-3000)
CodeBase: file:///C:/WINDOWS/assembly/GAC_MSIL/System.Xml/2.0.0.0__b77a5c561934e089/System.Xml.dll
----------------------------------------
Accessibility
Assembly Version: 2.0.0.0
Win32 Version: 2.0.50727.3053 (netfxsp.050727-3000)
CodeBase: file:///C:/WINDOWS/assembly/GAC_MSIL/Accessibility/2.0.0.0__b03f5f7f11d50a3a/Accessibility.dll
----------------------------------------
System.Data
Assembly Version: 2.0.0.0
Win32 Version: 2.0.50727.3053 (netfxsp.050727-3000)
CodeBase: file:///C:/WINDOWS/assembly/GAC_32/System.Data/2.0.0.0__b77a5c561934e089/System.Data.dll
----------------------------------------

************** JIT Debugging **************
To enable just-in-time (JIT) debugging, the .config file for this
application or computer (machine.config) must have the
jitDebugging value set in the system.windows.forms section.
The application must also be compiled with debugging
enabled.

For example:

<configuration>
<system.windows.forms jitDebugging="true" />
</configuration>

When JIT debugging is enabled, any unhandled exception
will be sent to the JIT debugger registered on the computer
rather than be handled by this dialog box.

wilsonmar Nov 11, 2008 at 7:29 PM 
Looks great. When do you think you'll have a version for SQL 2008 (SQL 10)?