Posted by: Karlo Bartels | 2009/03/12

SQLDoc Lite

Creating XML documentation for T-SQL

Download SQLDoc Lite

Popular tools like Sandcastle and DocProject allow you to create MSDN style help files for your projects by using the XML documentation file created by Visual Studio. SQL Server Management Studio lacks the ability to generate documentation files for database objects that can be used by the aforementioned tools. Let’s look at a way of generating such files by using plain-old T-SQL.

Documenting database objects

There are several ways to document database objects, but the easiest and most widely accepted method is by using extended properties. Right-click on any database object, click Properties and select the Extended Properties page. Create a property called Description and enter the documentation details.

Here’s a example for the Catalog table in the ReportServer database:

Add extended property

Retrieving documented objects

The next step is retrieving the documented objects by querying the sys.extended_properties table. This system table contains a row for each extended property of each object in the database. Retrieve this information in a CTE, so you can easily parse the results into an XML document.

Creating the XML file

To return result sets as XML instead of standard rowsets, you can execute SQL queries by using the FOR XML clause of the SELECT statement. I used the PATH mode to construct an XML document that contains the required nesting for an XML Documentation file.

SQLDoc Lite

My solution to generate XML Documentation files contains of the following procedures:

  • sqldoc_CreateXMLDocumentation: this sproc does all the hard work
  • sqldoc_ShowUndocumentedObjects: this one identifies any undocumented objects.

If you create these stored procedures in the master database, they will be available for every database that you create from that point on. These procedures will document the following objects only:

  • Tables
  • Views
  • Stored Procedures
  • Scalar-valued Functions
  • Table-valued Functions

Results

This is what a generated XML Documentation file looks like for the ReportServer database:

 XML Documentation

Added bonus

As a bonus, I included 2 stored procedures in the download package that generate HTML documentation files and plain documentation in the Results grid. The HTML output looks like this:

HTML Documentation

Happy documenting!

Download SQLDoc Lite

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Categories

%d bloggers like this: