Writing documentation is one of the most tedious tasks in the development process, and is usually pushed off to the end, if done at all. In Visual Studio, documentation is often added as inline comments, and compiled using a tool such as Ghost Doc or NDoc. Databases, however, are very difficult to document, since they do not have very good capabilities to hold textual metadata.

Two tools I have discovered recently take a lot of the pain out of documenting databases.  Developed by Logica2, dbdesc takes the pain from documenting database schemas, and its sister program, dtsdoc, produces comprehensive documentation for your DTS packages.

Whether you have a Microsoft Access, Firebird, SQL Server 2000 or 2005 database, in only three simple tabs and a few mouse clicks, dbdesc can produce documentation in either PDF or XML format. The XML format can then be transformed using the included XSLT, or ones you create yourself.

Since DTS packages are specific to SQL Server 2000, dtsdoc works only with SQL Server 2000.  Since SQL Server 2000 has recently entered the last three years of its support lifecycle, migration to more recent versions is imperative. Not all DTS's can be automatically upgraded to SSIS, so having good documentation will be a great asset in recreating the packages. 

In this review, we will use dbdesc to document the sample Northwind database from SQL Server 2000, and dtsdoc to document a simple DTS package.

Dbdesc can be found at www.dbdesc.com and dtsdoc is found at www.dtsdoc.com.

Database schemas are very difficult to document well. There are no mechanisms for storing large amounts of descriptive information, and retrieving schema information can be tedious. When changes are made, updating the documentation can be a daunting task. Dbdesc to the rescue!

Upon starting dbdesc, you are prompted for connection parameters. Provide the usual information--server name or IP, user credentials (SQL Server security or Windows authentication) and database name. You are one-third of the way there.

Figure 1

 

On the Objects tab, select the objects you want to include in your documentation.

Figure 2

 

Expanding Tables allows you to select primary and foreign keys, indices, triggers and check constraints to be included in your documentation. You can choose specific tables by using the Advanced button (see Bonus Feature section below).

Figure 3

 

The Output tab lets you select the report format and output file names.

Figure 4

 

The name of the supplied XSL template is added to the XML file, so the documentation can be opened in a readable format and transformed by either a standard XSL or a custom one you make.

Figure 5

 

Click on the Generate button and in a few brief moments your database will be fully documented, in the format you selected. Documentation is very complete--the Northwind sample database generates a 45 page PDF.

 

 

Table Documentation

The table documentation begins with a summary of the tables in the database, including row counts and disk consumption.

Figure 6

Each table's individual details, including column names, types, keys and permissions are detailed on their own pages.

Figure 7

The DDL script to recreate the table is also documented.

Figure 8

 

View Documentation

Views are documented similarly to tables, but without the summary section. Also included in the documentation for a particular view is a list of related objects, such as tables or other views queried.

Figure 9

 

Stored Procedure Documentation

The documentation for stored procedures includes input and output parameters, in addition to the SQL statement(s) and related objects.

Figure 10

 

User and Role Documentation

Users, roles and role memberships are also documented. 

Figure 11

 

Bonus Feature: Extended Property Editor

SLQ Server can store descriptive data for tables and table columns in the MS_Description extended property. You can manually set and query these properties with the sp_addextendedproperty stored procedure, and retrieve them with fn_listextendedproperty(). 

Extended properties can be very useful for documenting tables and columns, but can be tedious to create and edit. Dbdesc includes an extended properties editor, and extended properties will appear in the resulting documentation.

On the Objects tab is an Advanced button. Clicking this button opens a dialog where you can select the specific tables you want to document, as well as add extended properties to tables and columns.

Figure 12

 

In the example below we have added several extended properties to the Categories table and a couple of columns.

Figure 13

 

The table's extended property appears below the table name, and column descriptions appear in the Description column. This is a useful feature, and makes documentation both more helpful and easier.

Figure 14

 

DTS packages can become very complex, with numerous transformations and ActiveX scripts, among other hidden details that can take a considerable amount of effort to reveal in total.  These buried details are where the power of DTS lies, so documenting these details is essential.

 

In this example we will document a simple DTS which archives orders and order details.  When you first start dtsdoc, you are prompted for login credentials.

Figure 15

 

On the Packages tab is listed all DTS's on the server. Here, we can select one or more packages to document.

Figure 16

 

On the output tab, you specify the filename and XSLT to transform the XML output. The Generate button starts the generation process and in a few short moments, our DTS is documented.

Figure 17

 

The final output includes an XML file, a folder of XSLT and images, and an HTML file you open to views the transformed XML.

Figure 18

Opening the HTML file displays the package documentation. All connections are listed, excluding the password for security.

Figure 19

Tasks are detailed, including a SQL statement if there is one.

Figure 20

Every transformation for the task is listed, showing the column mappings and what the transformation is.

Figure 21

 

To summarize the package, steps are listed in order and hyperlinked to the associated task. As steps are added to a DTS over time, the order of the listing may not match the execution order.  For larger packages, it might be a good idea to change the names of the tasks so they better represent the order of execution. A DTS may have multiple simultaneous execution paths, which are difficult to represent in a list form. Dtsdoc does not store a visual representation of the package, so you may want to maintain a screenshot library of your packages, in addition to the documentation produced here.

Figure 22

Those deep down step properties we discussed earlier are also documented, and hyperlinked to other parts of the documentation file.

Figure 23

 

In this article we have looked at two commercial software packages which can greatly aid the onerous task of documenting databases and DTS packages. Both programs are simple to use and generate output quickly. I have used both to communicate database documentation to other developers and consultants, and have been pleased with the ease and output. If your job includes creating and maintaining database documentation, these two programs deserve a look to see if they belong in your toolbox.