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

Related posts

Also in Hub

Building reusable table build scripts using SQL Prompt

You've been working on a query, function or procedure and you want to store the results of executing it in a table; maybe a table variable, or a temporary table. These all require column definitions, ...

Also in Product learning

Using SQL Data Compare to Synchronize Custom Error Messages

One way to offer a better experience to our application users is to anticipate likely mistakes they could make, such as when filling in a web form, and provide them with a meaningful error message tha...

Also in SQL Doc

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 yo...

Also about PowerShell

How to automatically provision sanitized data using SQL Clone, Data Masker and PowerShell

National and regional legislation, as well industry standards, dictate what an organization can and can't do with sensitive data, as well as how it needs to be handled, audited and protected. In addit...

Also about SQL Doc

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 inf...