13 March 2017
13 March 2017

Creating database documentation in ePub format using SQL Doc and PowerShell

Guest post

This is a guest post from Phil Factor. Phil Factor (real name withheld to protect the guilty), aka Database Mole, has 30 years of experience with database-intensive applications.

Despite having once been shouted at by a furious Bill Gates at an exhibition in the early 1980s, he has remained resolutely anonymous throughout his career.

He is a regular contributor to Simple Talk and SQLServerCentral.

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.

Tools in this post

SQL Doc

Document your databases automatically.

Find out more

Guest post

This is a guest post from Phil Factor. Phil Factor (real name withheld to protect the guilty), aka Database Mole, has 30 years of experience with database-intensive applications.

Despite having once been shouted at by a furious Bill Gates at an exhibition in the early 1980s, he has remained resolutely anonymous throughout his career.

He is a regular contributor to Simple Talk and SQLServerCentral.

Share this post.

Share on FacebookShare on Google+Share on LinkedInTweet about this on Twitter

You may also like

  • Article

    How to document multiple SQL Server databases using SQL Doc and PowerShell

    You can use SQL Doc’s command-line parameters to automate database documentation, but when you try to automate the process of documenting a group of databases on a server, they sometimes don’t give you enough control over your automated job. The GUI does offer far more settings but when you’re running an automated process, you can’t

  • Article

    Documenting your Database with SQL Change Automation

    It would be wrong to portray SQL Change Automation (SCA) as being suitable only for epic project deployments, of the sort described in my previous article. It can do smaller tasks as well. To demonstrate, I’ll show how you can use SCA to check that the source code builds a database, and to provide a

  • Article

    Getting started with SQL Doc and PowerShell

    SQL Doc keeps everyone in the development team informed about a database. It provides more information than what is in source control, including server settings, and it formats and color-codes the information so that it is as easy to read as possible. It generates the source of all of the metadata of a database, along

  • Forums

    SQL Doc Forum

    Document SQL Server databases