My development life would be easier too since I wouldn’t have to think about the kind of data I was storing in my columns; I could define everything as VARCHAR(8000) once and never have to go back to make any changes. Brilliant!
While I was correct about not wasting storage space, it turns out the idea of making every column VARCHAR(8000) is a terrible idea.
This post is a response to this month’s T-SQL Tuesday #110 prompt by Garry Bargsley. T-SQL Tuesday is a way for the SQL Server community to share ideas about different database and professional topics every month.
This month’s topic asks to share how we automate certain processes.
I’m a fan of keeping documentation close to the code. I prefer writing my documentation directly above a procedure, function, or view definition because that’s where it will be most beneficial to myself and other developers.
Not to mention that’s the only place where the documentation has any chance of staying up to date when changes to the code are made.
What drives me crazy though is making a copy of that documentation somewhere else, into a different format. You know, like when someone without database access needs you to send them a description of all of the procedures for a project. Or if you are writing end-user documentation for your functions and views.
Not only is creating a copy of the documentation tedious, but there is no chance that it will stay up to date with future code changes.
So today I want to share how I automate some of my documentation generation directly from my code.
/// Retrieves the details for a user.
/// <param name="id">The internal id of the user.</param>
/// <returns>A user object.</returns>
public User GetUserDetails(int id)
User user = ...
I like this format: the documentation is directly next to the code and it is structured as XML, making it easy to parse for other uses (eg. use a static document generator to create end-user documentation directly from these comments).
This format is easily transferable to T-SQL:
<summary>Retrieves the details for a user.</summary>
<param name="@UserId">The internal id of the user.</param>
<returns>The username, user's full name, and join date</returns>
CREATE PROCEDURE dbo.USP_SelectUserDetails
SELECT Username, FullName, JoinDate FROM dbo.[User] WHERE Id = @UserId
<summary>Returns the value 'A'.</summary>
<param name="@AnyNumber">Can be any number. Will be ignored.</param>
<param name="@AnotherNumber">A different number. Will also be ignored.</param>
<returns>The value 'A'.</returns>
CREATE FUNCTION dbo.UDF_SelectA
Sure, this might not be as visually appealing as the traditional starred comment block, but I’ve wrestled with parsing enough free formatted text that I don’t mind a little extra structure in my comments.
Querying the Documentation
Now that our T-SQL object documentation has some structure, it’s pretty easy to query and extract those XML comments:
WITH DocumentationDefintions AS (
SCHEMA_NAME(o.schema_id) as schema_name,
o.name as object_name,
CAST(SUBSTRING(m.definition,CHARINDEX('<documentation>',m.definition),CHARINDEX('</documentation>',m.definition)+LEN('</documentation>')-CHARINDEX('<documentation>',m.definition)) AS XML) AS Documentation,
p.parameter_id as parameter_order,
p.name as parameter_name,
t.name as parameter_type,
INNER JOIN sys.sql_modules m
ON o.object_id = m.object_id
LEFT JOIN sys.parameters p
ON o.object_id = p.object_id
INNER JOIN sys.types t
ON p.system_type_id = t.system_type_id
o.type in ('P','FN','IF','TF')
t.c.value('author','varchar(100)') as Author,
t.c.value('summary','varchar(max)') as Summary,
t.c.value('returns','varchar(max)') as Returns,
p.c.value('@name','varchar(100)') as DocumentedParamName,
p.c.value('.','varchar(100)') as ParamDescription
OUTER APPLY d.Documentation.nodes('/documentation') as t(c)
OUTER APPLY d.Documentation.nodes('/documentation/param') as p(c)
p.c.value('@name','varchar(100)') IS NULL -- objects that don't have documentation
OR p.c.value('@name','varchar(100)') = d.parameter_name -- joining our documented parms with the actual ones
This query pulls the parameters of our procedures and functions from sys.parameters and joins them with what we documented in our XML documentation. This gives us some nicely formatted documentation as well as visibility into what objects haven’t been documented yet:
Only the Beginning
At this point, our procedure and function documentation is easily accessible via query. We can use this to dump the information into an Excel file for a project manager, or schedule a job to generate some static HTML documentation directly from the source every night.
This can be extended even further depending on your needs, but at least this is an automated starting point for generating further documentation directly from the T-SQL source.