T-SQL Code Documentor

Eugene Niemand
Jun 11, 2013 · 3 min read

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:

T-SQL Documentation Code
T-SQL Documentation Code

Here is the generated documentation

TSQLDocumentation
TSQLDocumentation

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

Eugene Niemand’s Blog

Eugene Niemand

Written by

Lead Data QA Engineer at ASOS.com - I have a passion for Test Driven Development, Agile Methodologies, Continuous Integration and Delivery using Microsoft Azure

Eugene Niemand’s Blog

Technology and Programming professional and enthusist

Eugene Niemand

Written by

Lead Data QA Engineer at ASOS.com - I have a passion for Test Driven Development, Agile Methodologies, Continuous Integration and Delivery using Microsoft Azure

Eugene Niemand’s Blog

Technology and Programming professional and enthusist

Medium is an open platform where 170 million readers come to find insightful and dynamic thinking. Here, expert and undiscovered voices alike dive into the heart of any topic and bring new ideas to the surface. Learn more

Follow the writers, publications, and topics that matter to you, and you’ll see them on your homepage and in your inbox. Explore

If you have a story to tell, knowledge to share, or a perspective to offer — welcome home. It’s easy and free to post your thinking on any topic. Write on Medium

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store