T-SQL Code Documentor

I recently resigned and was tasked to document all the important stored procedure and triggers. Yes I know this should have been done at the time but we have never had time, or should I rather say it’s never been priority.

I was deliberating over the different methods possible to document the T-SQL code. Red-Gate has SQL Doc but that only updates extended properties. I thought I could use Word and document what every Stored Proc or Trigger does but that document will be dead in the water. When making changes some of this come to mind… “Oh I need to update the documentation”… “Where is the documentation saved again?”… “Aargh!!! The other guy did not document his changes”. The last two can be mitigated by using source control or versioned docs like Google Docs or Sharepoint etc. and a bit of discipline.

The worst part is, how do you tie sections of the code to sections of the document?

Then I thought I can document the code by adding standard T-SQL comments. This makes sense as the documentation then exists inside the code at the exact point where it matters or explains something; this is sometimes referred to as “Living Documentation”.

Great, this solves my problem. Hold on, not so fast, how do business users easily view the documentation? Yes we can maybe install SSMS and grant them read only privileges but they won’t know what procedures to go look for when something breaks. They can’t search or browse all documentation. Heck, even if they did know the answer to all the above, will they know what for or where in the code to look?

I figured, I can get the definition of every object from SQL Server. I can then parse the definition text and extract the comments. The comments will need some structure; I can’t simply read all commented text as it will also extract commented code etc. It will just be a mess. I searched for a solution that implements the same documentation methods you find in C#, but I could not find anything simple and easy to use. Also none of the solutions did it the way I wanted to do it.

I wanted something simple, unrestrictive and easy to use. My only option is to build my own solution it can be found on codeplex https://tsqlcodedocumentor.codeplex.com/

Here is a short explanation of what T-SQL Code Documentor does:

• Consumes the supplied Definition text (See MVC Section below)
• Using RegEx search for any text in the HTML/XML format <Tag>Some Text</Tag>
• Validate that all tags have matching Opening and Closing tags.
• Read the text between the tags and do a bit of scrubbing.
• Return a Markdown formatted string.

I have a separate MVC project:

• It provides a connection string and reads all the objects (Schema, Name, Type and Definition)
• It then Calls the T-SQL Code Documentor ExtractTagContents method for every object
• Create a Markdown Heading for every Object
• Append the return string from ExtractTagContents
• Return an HtmlString to the View.

And there you have it, all your database documentation on a web page that always displays the latest version and can easily be consumed by normal business users.

Here is an example of the comments:

Here is the generated documentation

I used John Gruber’s Markdown syntax http://daringfireball.net/projects/markdown/

I used Brian Jeremy’s version of a ported .Net Markdown library to generate the HTML