Phil Factor

13 March 2017

• SQL Doc
• Database Documentation

Creating database documentation in ePub format using SQL Doc and PowerShell

With SQL Doc and a bit of PowerShell, you can automatically generate database documentation in a range of formats, including help files, Markdown files, Word documents, or an intranet site of HTML files. Sometimes, however, SQL Doc can’t give you the documentation format that you want, out-of-the-box.

In these cases, you have to roll up your sleeves and think in terms of scripting a toolchain that takes the output of SQL Doc and turns it into something else. For example, we can use pandoc to turn the document produced by SQL Doc into virtually any format you care to name.

In this article, I’ll demonstrate a PowerShell script that takes .docx output from SQL Doc and then uses pandoc to produce a file in ePub format.

SQL Doc command line switches

We can use SQL Doc’s command line interface to run PowerShell scripts that automatically generate database documentation. My previously-referenced article ends with a reference guide to the command line options.

There is one problem, however: there is no command-line switch to specify the name of the output file from SQL Doc; it constructs it on-the-fly, from the name of the server and the time.

Fortunately, SQL Doc can send its console output, which will include the output file name, to a file that we specify. If all goes well, this file tells us what file has been successfully produced, and we just need to parse this file to extract it, which is fairly easy with PowerShell.

Automating ePub database documentation using PowerShell

There are a number of ways to go about this. My previous article showed how to use an existing SQL Doc project file and then specify additional command line options, such as the server name and database name.

In this example, shown in Listing 1, we don’t bother with a project file; we simply call sqldoc, specifying the name of the server and the database, and that the output should be a .docx file. We parse the console output (called WhatHappened.txt in Listing 1) to get the name of the .docx file and then call pandoc to convert it to ePub format.

You will, of course, need to install pandoc, but if you already use RStudio, you’ll find it in a subdirectory. Pandoc is a very useful utility, though it needs patience: some types of conversion need a lot of command-line switches to get a good result.

You’ll need to adapt the code to specify the location of pandoc, the server and database names, and the directory in which to place the console output and the final ePub.

Listing 1

We can adapt this script to provide a list of databases, but make sure that there is just a comma, no space, between the items in the list of databases to document. By specifying a list of databases, you will end up putting them in one file, which may not be what you wanted. Listing 9 of my previous article demonstrates how to run SQL Doc on a list of databases, with separate output files for each database, and same technique would work here.

Summary

SQL Doc is useful as a standalone database documentation tool, but it becomes significantly more powerful when you start to think of automatic database documentation within a broader toolchain.

Here, I’ve kept the code simple in order to demonstrate the principles. However, with some error-handling and logging, you can add this script to an existing toolchain that implements continuous integration and start to automate the whole process of building the latest version of a database from source control, generating up-to-date documentation, filling the database with data, and running your database tests.

To find out how to get started with SQL Doc and PowerShell, or how to document multiple SQL Server databases using SQL Doc and PowerShell, see my other two posts in this series.

And remember, if you’d like to explore SQL Doc further, you can download a 14-day, fully-functional free trial.