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
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
...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):
<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 trigger will appear |
<SUMMARY> |
the description for the trigger |
<REMARKS> |
remarks on the usage of this trigger |
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):
<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 UDF will appear |
<SUMMARY> |
the summary description for the UDF |
<PARAM_IN name="@param1"> |
the description for @param1 input parameter; you have to describe each input parameter similarly |
<RETURN_VAL> |
the description for the return value of the UDF |
<REMARKS> |
remarks on the usage of this UDF |
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):
<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 UDF will appear |
<SUMMARY> |
the summary description for the UDF |
<PARAM_IN name="@param1"> |
the description for @param1 input parameter; you have to describe each input parameter similarly |
<RETURN_RES> |
the description for the return resultset of the UDF |
<REMARKS> |
remarks on the usage of this UDF |
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 "<" and ">".
Also, avoid "&" but use "&".
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? :)