Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Creating Stored Procedure Documentation with XML, SQL-DMO and XSLT

By Morten Wittrock,

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:

Doc commentContains
#descA summary description of the stored procedure
#authorAuthor information
#versionVersion information
#paramA 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
Author Morten Wittrock
Version 1.0

Name Datatype Length Direction Description
@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.

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


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.

Total article views: 14602 | Views in the last 30 days: 32
 
Related Articles
FORUM

DOCUMENT

DOCUMENT REQD.

FORUM

Documention

Instance and DB documentation

FORUM

SSRS / SSAS Params Passing thru DOTNET

SSRS / SSAS Params Passing thru DOTNET

ARTICLE

Documenting Stored Procedures

Regular columnist Robert Marda discusses a few ideas on stored procedure documentation. How much doc...

FORUM

How can I document reports (RDL) files?

Documenting or parsing reports RDL/RDS files

Tags
schemas/dtds    
stored procedures    
t-sql    
xml    
xsl    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones