Creating stored procedure documentation with XML, SQL-DMO and XSLT
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:
|A summary description of the stored procedure|
|A parameter name followed by a description of the parameter|
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:
|Description||Created with the purpose of demonstrating TSQLDoc documentation comments|
|@param1||int||4||in||Contains an integer|
|@param2||varchar||50||in||Contains a string|
|@param3||datetime||8||in||Contains a date|
|@outparam||int||4||inout||Integer output parameter|
Go here to view the complete
pubs documentation page.
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.
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.