DBdoc: automated CHM documentation for a SQL Server 2000 database

Download source code - 114.9 Kb

Introduction

Do you know the Visual Studio .NET capability of extracting - at compile time - some formatted comments (you included in your source code) in the form of XML descriptive files, that you can later process with programs like NDoc in order to produce a CHM documentation of your assemblies and classes?

Well, if your answer is "yes", and if you ever felt the lack of a similar feature in the Microsoft SQL Server 2000 suite, then you will easily understand the goal of DBdoc, the tool presented in this article. It arises from the need of producing, in a similar automated way, the documentation for a SQL Server 2000 database, by extracting some properly formatted comments included in the tables, stored procedures, user-defined functions and triggers of the database, and by combining them with some SQL Server metadata in order to produce a collection of descriptive HTML files useful to build the CHM documentation of your database.

If you don't know the XML commenting features of Microsoft Visual Studio .NET, my suggestion is to read this before proceeding:
http://msdn.microsoft.com/msdnmag/issues/02/06/XMLC
http://www.codeproject.com/csharp/csharpcodedocumentation.asp

If you're interested in the XML code documentation for .NET programming languages, keep in mind that in some versions of Microsoft Visual Studio this feature is present in the C# language editor context only; in order to have a similar feature in VB.NET language, you can use third-party tools like the free Visual Studio add-in named "VBCommenter" (see http://www.codeproject.com/vb/net/VbCommenter.asp).

A first look to DBdoc

The main idea behind DBdoc is to make easy the CHM documentation preparation for your Microsoft SQL Server 2000 database through collecting some comments and descriptions you left into the database itself. Being your database and its description indivisible, you'll mantain the documentation updated easily, while modifying the database objects, their structure and the T-SQL code.

The input for DBdoc is a properly commented SQL Server 2000 database (in the following, we'll call it for simplicity "ToBeDoc database", the "database to be documented"); its output is a CHM help project ready to be compiled with the "Microsoft HTML Help Workshop" in order to produce the actual CHM file. For information about "HTML Help Workshop", normally shipped with Visual Studio .NET, visit http://www.microsoft.com/downloads/details.aspx?FamilyID=00535334-c8a6-452f-9aa0-d597d16580cc&DisplayLang=en (I used the version 4.74.8702.0 of "HTML Help Workshop", that comes with Microsoft Visual Studio .NET 2003).

In order to extract the needed information from the ToBeDoc database, DBdoc executes some queries on the database metadata (i.e. system tables such as sysobjects and syscolumns, INFORMATION_SCHEMA views and so on).

The extraction of XML comments from stored procedures, user-defined functions and triggers (see below for a description about XML commenting guidelines) is done through a syntax parsing of the T-SQL code that implements them. The T-SQL parser has been built using the wonderful Spart library by Jonathan de Halleux (that I wish here to thank for this great work).

How DBdoc works

The support database

DBdoc makes use of a simple support database where it stores temporary data (needed for internal computation) collected from the database to be documented ("ToBeDoc database"). This support database (we'll call it simply "DBdoc database") is made up of the following tables:

• RawRoutines and RawParameters: containing raw information about the T-SQL "routines" (that is: stored procedures, user-defined functions and triggers) hosted in the ToBeDoc database, along with their parameters;
• RawTables and RawColumns: containing raw information about the tables hosted in the ToBeDoc database, along with their columns;
• Routines, Params, Tables and Columns: containing similar (but computed, not raw) information about the objects hosted in the ToBeDoc database;
• RoutinesXref: containing cross-reference information about T-SQL routines of the ToBeDoc database;
• ExcludedRoutines and ExcludedTables: eventually containing the list of the objects (routines and tables) you want to omit in the final resulting CHM documentation.

The difference between the Routines, Params, Tables and Columns tables and their Raw counterparts is that Raw tables collect a sort of raw "dump" of ToBeDoc database metadata, while not-Raw tables contain similar data enriched and pre-computed to be ready for the CHM project files generation.

To make it clear, for example the RawTables table contains the fields TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME and TABLE_TYPE (directly coming from a "SELECT * FROM INFORMATION_SCHEMA.TABLES" command executed against the ToBeDoc database), while the Tables table contains fields like Comment (computed later by inspecting the extended properties of each specific table object listed).

The source code ZIP file includes a T-SQL script (named CreateDB.sql) useful to create the DBdoc support database.

The user interface

The DBdoc user interface consists in the following single Windows Form:

The data you're asked to type in, needed for the documentation creation, are:

Help Project filename	The filename to be assigned to the HTML Help Workshop project file produced by DBdoc (that will have an .HHP extension) and - after compilation - to the final documentation file (that, of course, will have a .CHM extension).
Output folder	The folder where you want the HTML Help Workshop project files will be saved (the folder is created if it doesn't exist). You can either specify a full, absolute folder path (such as "C:\Northwind") or a relative folder path (that will refer a folder under the directory where DBdoc is currently running).
Help Documentation Title	The title you want on the title bar of the Help viewer when reading your final CHM:
Root node title	The title you want to appear in the root node of the CHM document hierarchy:
Root node filename	The filename of the HTML content file that will appear by clicking the root node in the CHM document hierarchy: This content document normally introduces the CHM document content as a whole, and has to be supplied manually, because it is not inferable from the ToBeDoc database metadata or comments. You can either simply specify a filename (such as "Introduction.htm") or a full, absolute file path (such as "C:\MyFiles\Introduction.htm"): in the first case, you will have to ensure that the file actually exists on the output folder before compiling the CHM project with HTML Help Workshop; in the latter case, DBdoc will automatically copy the file you specified from its original location to the output folder.
Page Header	The header text you want to appear on each page of the final CHM document:
Page Footer and Updating date or version	The text you want to appear in the footer line of each page of the final CHM document.
Database to be documented	The connection string details (server name, database name, username and password of a valid SQL Server login) to access the ToBeDoc database and its metadata.
DBdoc database	The connection string details (server name, username and password of a valid SQL Server login) to access the DBdoc support database (always named "DBdoc", by design).
Comments for Tables/Fields in	This option allows you to specify the source for comments related to the tables to be documented and their fields. Currently, DBdoc supports the retrieval of such information either from the SQL Server standard extended properties (see below) or from a DDL file (containing the ToBeDoc database data model) generated with Microsoft Visio.
Collect cross reference info	A flag indicating if you want (or don't want) to detect cross-reference info in the ToBeDoc database objects set and to include them in the final CHM documentation.
Enable errors logging	A flag indicating if you want (or don't want) to log unespected errors encountered during the execution of DBdoc. Whit this option enabled, in the folder where DBdoc runs, an HTML file named LogFile.htm will be created to collect any exception raised during DBdoc computations; this could be very useful to diagnose the presence of something wrong in the comments you included in the ToBeDoc database. The log file is in HTML format just to be easily read in a browser.

All the information you supply in the UI form are persisted for your future DBdoc session through the well-known ConfigOpt class.

Processing steps to produce your database documentation

To produce your database CHM documentation, follow these steps:

• describe your database objects by inserting descriptions and comments, following the guidelines in the paragraph "How to comment your database" below;
• edit an introductory HTML document to be displayed in the CHM "home page" (see the file welcome.htm in the sources for an example);
• verify that the DBdoc support database is correctly installed on your SQL Server; if not, use the CreateDB.sql script to create it;
• run DBdoc and fill the form with the proper parameters;
• click on the "Analyze" button and wait until DBdoc completes the ToBeDoc database inspection and data collection;
• click on the "Generate" button and wait until DBdoc completes the creation of the documentation help project files;
• run the "HTML Help Workshop", load the help project file (.HHP) generated by DBdoc and launch the compilation;
• if the compilation runs without errors, you'll get your final CHM documentation file;
• if some errors occurred during help project compilation, check the DBdoc error log file in order to diagnose them.

Notes on the final layout

DBdoc makes use of the msdn.css cascading stylesheet, in order to produce a standard MSDN-like documentation. The actual HTML pages generated by DBdoc and then used by the "HTML Help Workshop" to build the final CHM documentation file are produced by applying some XSLT transformation stylesheets (look for *.XSLT files in the bin folder) to the actual data extracted from the ToBeDoc database. This means that the final layout of the produced CHM documentation file is fully customizable by modifying these XSLT and CSS files.

How to comment your database

As stated earlier, DBdoc builds the documentation files by retrieving the descriptive comments you wrote inside your SQL Server 2000 database.

In the following paragraphs you'll find some guidelines for supplying those data in the correct way.

Commenting Tables and Fields

The best way for supplying comments for tables and fields to DBdoc is through SQL Server extended properties; this is also the recommended way, because it keeps the documentation close to the database objects. SQL Server extended properties for a table and its fields are simply editable by using Microsoft Enterprise Manager: just put the comment of each field in the Description textbox shown in the design view of a table, and put the comment for the table itself in the Description textbox of its Properties dialog box.

DBdoc also supports the retrieval of table and fields comments from a DDL Microsoft Visio-generated file, but I won't describe this feature in details (also because it is just sketched and it is not designed to work with "any" version of Visio files).

Commenting Stored Procedures

In order to supply comments for the stored procedures hosted in the ToBeDoc database, you have to include in the code implementing each of them a properly formatted XML comment, following this example:

CREATE PROCEDURE CustOrderHist @CustomerID nchar(5)
AS
/*
<DOC>
  <DOCSECTION>>Customers' functionalities</DOCSECTION>
  <SUMMARY>Retrieves the complete order history for a given Customer.</SUMMARY>
  <PARAM_IN name="@CustomerID">The ID of the Customer you're interested in.
</PARAM_IN>
  <RETURN_RES>A list of bought items in the form of a resultset. Each row 
contains: ProductName, 

TotalQuantity.</RETURN_RES>
  <REMARKS>Please note that the returned resultset is unordered.</REMARKS>
</DOC>
*/
SELECT ProductName, Total=SUM(Quantity)
FROM Products P, [Order Details] OD, Orders O, Customers C
WHERE C.CustomerID = @CustomerID
AND C.CustomerID = O.CustomerID AND O.OrderID = OD.OrderID 
AND OD.ProductID = P.ProductID
GROUP BY ProductName

As you see, the XML fragment is inside a T-SQL comment (denoted by /* and */), and it follows the AS keyword.

The comment must describe your code in detail. Because stored procedures not only can receive input parameters and give out a single resultset (like in the above example) but also can return output parameters, integer return values, multiple resultsets or XML streams, the nodes you have to include in the descriptive XML comment will vary. The following table lists all the node types you can put in the descriptive comment of a stored procedure in order to feed DBdoc with the proper information (see the SP_comment.xml file in the downloadable ZIP for an example):

<DOCSECTION>	the "path" (with parts delimited by backslashes) that describes where, in the CHM topics hierarchy (the "Table of contents" of the final CHM document), the description of this stored procedure will appear. For example, by specifying a <DOCSECTION> of "FRC\Tree" for the xnetFRCInit stored procedure, and a <DOCSECTION> of "FRC" for the stored procedures xnetRFCMenuGet and xnetFRCTreeMenuCreate, you obtain this organization of topics in the final CHM:
<SUMMARY>	the summary description for the stored procedure
<PARAM_IN name="@param1">	the description for @param1 input parameter; you have to describe each input parameter similarly
<PARAM_OUT name="@param2">	the description for @param2 output parameter; you have to describe each output parameter similarly
<PARAM_INOUT name="@param3">	the description for @param3 input/output parameter; you have to describe each input/output parameter similarly
<RETURN_RES>	the description for the first returned resultset; you have to repeat this node for each resultset, in case of multiple resultsets returned
<RETURN_XML>	the description for the first returned XML; you have to repeat this node for each XML stream, in case of multiple XML streams returned
<RETURN_VAL value="0">	the description for the integer return value of zero; you have to repeat this node for each possible value passed back as a return value
<REMARKS>	remarks on the usage of this stored procedure

Commenting Triggers

To supply a descriptive comment for a trigger, you have to proceed in the same way you did for stored procedures. Put the XML comment in the code implementing the trigger as shown here:

CREATE TRIGGER [TriggerName] ON [TableName] 
FOR INSERT, UPDATE, DELETE 
AS
/*
<DOC>
...descriptive XML comment...
</DOC>
*/
...trigger code...

The node types you can put in the descriptive comment of a trigger are listed here (see the TR_comment.xml file in the downloadable ZIP for an example):

Commenting User-Defined Functions

Again, to supply a descriptive comment for an UDF, you have to proceed in the same way you did for stored procedures and triggers. Even if the syntax for the various types of UDF differs (for Scalar Functions, Inline Table-valued Functions, Multi-statement Table-valued Functions), you have to put the XML descriptive comment in the code implementing the UDF, just after the AS keyword.

Node types you can put in the descriptive comment of a Scalar Function (see the UDFscalar_comment.xml file in the downloadable ZIP for an example):

Node types you can put in the descriptive comment of an Inline Table-valued Function or of a Multi-statement Table-valued Function (respectively, see the UDFinline_comment.xml and the UDFmultistat_comment.xml files in the downloadable ZIP for an example):

Notes on XML comments

The position where to put the XML descriptive comment in your T-SQL code is important: I recommend to put it after the AS keyword because if you use some source control tools, such as SqlXPress, comments outside the CREATE statement could be cut off during saving/retrieval (checkin/checkout) operations.

Don't use "<" and ">" inside the descriptive text of your comment (or you will break the correctness of the XML comment fragment): use instead "&lt;" and "&gt;".
Also, avoid "&" but use "&amp;".

Don't insert carriage returns in the <DOCSECTION> node text (this would break the "path" inside the CHM table of contents).

I suggest to keep the <SUMMARY> node text very short (as inside MSDN documentation): you can go in more details in the <REMARKS> text.

Points of interest

The two main points of interest in DBdoc are the T-SQL parser implementation with the Spart library, and the advanced use of SQL Server metadata access.
All the rest is really only a quite practical XML processing and a very simple HTML generation.

Future enhancements

DBdoc is quite far to be a perfect and complete tool. It has to be improved in various areas and extended in many features. Some of them could be the subject for future enhancements.

• Currently, for example, there is no support for documenting database VIEWS. Also, DBdoc doesn't manage any T-SQL code created with the ENCRYPTION option. Another weakness of DBdoc is in the cross-reference detection algorithm; it simply looks for known object names inside the T-SQL code of other objects (see the FindObjectNameInScript procedure): this approach is very simple and is not fully reliable, especially when T-SQL code contains dynamic SQL (then, some cross-references could be missing).
• Currently, DBdoc has no UI that supports the editing of the ExcludedRoutines and ExcludedTables tables.
• Currently, DBdoc doesn't check about correspondance between actual parameters in the routines and the ones you documented in the XML comments.
• Currently, you have to manually invoke the "HTML Help Workshop" compiler in order to produce the final CHM file: DBdoc could perform this step automatically, as other products like NDoc do.
• Finally, of course, DBdoc is designed for SQL Server 2000 and it doesn't support SQL Server 2005; modifying DBdoc to support the new version of Microsoft SQL Server is not so simple, because of new syntax, new metadata storage, and so on... Do you accept the challenge? :)