Ah, documentation – how do we hate thee?
If you’re anything like me, you’re continually frustrated by the lack of adequate documentation about the databases you have to work with, but don’t have the time (or inclination) to write it yourself. Fortunately, there are tools available to help with that. Today, in order to help out a colleague who was trying to figure something out, I fired up Red Gate’s SQL Doc to provide him with a full set of documentation for a problematic database.
Of course, it is possible to build this documentation yourself, by painstakingly going through every object in your database (or collection of databases); however, this could take days, and be unreliable as there’s always the possibility of missing something, even in a relatively small database.
Launching SQL Doc
Nice and easy – it’s there on the SSMS Object Explorer right-click menu. Here, you can see I’ve started at the database level, which is probably where most people start. It’s also on the menu shown for individual database objects. Unfortunately, it’s not available at anything above a single database – you can’t select a group of related databases, for example, or even the whole server.
Once SQL Doc has loaded, you can select which objects to document, and (I like tihs bit) set the MS_Description field for each object at this time:
We’re just a couple of clicks away from actually having some documentation:
Output Options
There are various output options, two for interactive browsing, and two for printing or distribution.
- HTML – a set of HTML files, all hyperlinked and ready to roll
- DOCX – A Microsoft Word document. You have some control over the page size
- CHM – A Compiled Help file (this needs the Microsoft Help Workshop – fortunately, SQL Doc will take you to the right place to get this
- PDF – again, you have control over the page size.
The “Generate timestamp” option does just that – adds the date/time to the end of the filename (or directory name, in the case of the HTML option). Here’s what the HTML output looks like
So far, so good. And it is good. However…
Dynamic SQL in objects
If your database design makes extensive use of dynamic SQL in its stored procedures, then the documentation generated won’t have the appropriate links in it:
I have reported this back to Red Gate. I’m not the only one to have mentioned this, apparently, and I can understand that it might not be the simplest feature to add! Still, fingers crossed…
Permissions
So, what about the permissions required by the tool? Again, top marks to Red Gate – they have documented the required permissions on their help pages, which is more than can be said for the last lot I talked about!
The Beancounting
To run SQL Doc and generate as much documentation as you could possibly hope for takes about five minutes. By my reckoning, generating that level of documentation would take an hour an object, at least, so for any non-trivial database, it’s a *lot* of tedious, error-prone manual work. At the time of writing (April 2014), SQL Doc costs about UK£225 / US$370. How much do you pay the guys who write your documentation? I’m betting that that money wouldn’t buy two days of time from your in-house documentation gurus, let alone your DBA. Not sure if it’s for you? There’s a 14 day free trial so you can find out.
Small Print
Disclosure: I am a Friend of Red Gate, which means they provide me with software to try out; however, they haven’t asked me to write this review, and neither do they directly pay me. I write about their software because I want to, and I like it (the software).
