Creating stored procedure documentation with XML, SQL-DMO and XSLT

Introduction

Embedded documentation comments are a convenient and effective way of documenting your code, while its intricacies are still fresh in memory. A number of development environments and languages, Java and C# being prominent examples, have support for them. SQL Server, unfortunately, does not.

This article introduces TSQLDoc, a Windows Script Host script that extracts procedure metadata and embedded documentation comments from Transact-SQL stored procedures, generates XML documentation for every stored procedure in a database and optionally transforms the XML document into HTML or other format using XSLT. I will briefly touch upon the inner workings of the software, and give examples of its use.

How does it work?

TSQLDoc supports the following single line documention comments:

When you've added the appropriate comments to your stored procedure, TSQLDoc extracts the procedure's text and the name, datatype and direction of its parameters using SQL-DMO (1). The documentation comments are then pulled from the Transact-SQL code using VBScript regular expressions, and an XML document is created using the objects provided by MSXML. The XML documents produced by TSQLDoc all conform to the document model defined in tsqldoc.dtd (2).

Running the script

Let's take the script for a test drive, using the venerable pubs database as an example. First, download TSQLDoc.vbs, and make sure that your system meets the system requirements (3). Next, run the script by executing the following command at the prompt: cscript TSQLDoc.vbs -user [your username] -pwd [your password] -db pubs -out pubs.xml

TSQLDoc will then connect to the local server, extract the information it needs from the pubs database, build an XML document and write it to the file pubs.xml. The XML document contains an <sp> element for each stored procedure in the database. Here's an example:

<sp name="reptq3">
  <parameters>
    <param name="@lolimit" datatype="money" length="8" direction="in"/>
    <param name="@hilimit" datatype="money" length="8" direction="in"/>
    <param name="@type" datatype="char" length="12" direction="in"/>
  </parameters>
</sp>

Now, let's add a new procedure, containing documentation comments, to the database. Connect to your local server, and execute the following statement in the pubs database (4):

CREATE PROCEDURE DocExample (@param1 INTEGER, @param2 VARCHAR(50), @param3 DATETIME, @outparam INTEGER OUTPUT)
AS
    -- #author  Morten Wittrock
    -- #version 1.0
    -- #desc    Created with the purpose of demonstrating TSQLDoc documentation comments
    -- #param @param1    Contains an integer
    -- #param @param2    Contains a string
    -- #param @param3    Contains a date
    -- #param @outparam  Integer output parameter
    -- No actual code needed for this example

Running the script again, you'll find the following <sp> element added to the generated XML document:

<sp name="DocExample" version="1.0">
  <author>Morten Wittrock</author>
  <description>Created with the purpose of demonstrating TSQLDoc documentation comments</description>
  <parameters>
    <param name="@param1" datatype="int" length="4" direction="in">
      <description>Contains an integer</description>
    </param>
    <param name="@param2" datatype="varchar" length="50" direction="in">
      <description>Contains a string</description>
    </param>
    <param name="@param3" datatype="datetime" length="8" direction="in">
      <description>Contains a date</description>
    </param>
    <param name="@outparam" datatype="int" length="4" direction="inout">
      <description>Integer output parameter</description>
    </param>
  </parameters>
</sp>

Producing HTML documentation

TSQLDoc lets you transform the generated documentation into HTML (or other formats, for that matter) using XSLT (5). Let's apply a sample XSLT stylesheet called transform.xsl to the pubs documentation, using the -xslt command line parameter: cscript TSQLDoc.vbs -user [your username] -pwd [your password] -db pubs -xslt transform.xsl -out pubs.html

TSQLDoc transforms the XML document according to the rules defined in transform.xsl, and saves the result in the file pubs.html. Here's the DocExample documentation, formatted in HTML:

Go here to view the complete pubs documentation page.

Final words

TSQLDoc is a basic implementation of documentation comments for SQL Server. It has plenty of room for new features, and I encourage anyone to modify and improve the software. Here's a few suggestions:

• Add support for database objects other than procedures and functions
• Add a repository for storing metadata about tables, table columns, views, constraints etc.
• Add support for user defined functions
• Add a report showing database objects that are yet to be documented

Feel free to mail me with your suggestions, bug reports and comments.

Have fun!

Downloads

• TSQLDoc.vbs
• tsqldoc.dtd
• transform.xsl

1: SQL-DMO (SQL Distributed Management Objects) is a set of objects, that enable SQL Server developers to programmatically access database objects and perform a range of management tasks. Go here to learn more about SQL-DMO.

2: TSQLDoc reads the document model from tsqldoc.dtd at runtime, and places it in the internal subset of the generated XML document.

3: In order to run TSQLDoc, you'll need to have the following software installed on your system: Microsoft XML Core Services 4.0 (downloadable from http://www.microsoft.com/xml), VBScript version 5.0 or later (downloadable from http://msdn.microsoft.com/scripting) and the SQL-DMO objects (installed with SQL Server by default).

4: You can return the pubs database to its original state by running the instpubs.sql script, which you'll find in the Install directory below the SQL Server root directory.

5: XSLT (Extensible Stylesheet Language Transformations) is a language for transforming XML documents into other XML documents, HTML or text.Go here to learn more about XSLT.