13 March 2017
13 March 2017

Creating database documentation in ePub format using SQL Doc and PowerShell

Phil Factor provides a PowerShell script that uses SQL Doc produce database documentation and then pandoc to publish it in ePub format.

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

    Monitoring SQL Server Security: What's Required?

    If we know how a database is likely to be attacked, we can arrange our lines of defense, and install the monitors required to detect any attempts. However, some types of attack are difficult to imagine, so we also need our monitoring tool to be adaptable, so that it can collect a more diffuse collection of metrics, and then help us determine the reason for any sudden change in the patterns of access .

  • Webinar

    How DevOps can help you stay ahead of the competition

    In this webinar, Microsoft MVP Steve Jones welcomes DevOps expert Bob Walker, Solution Architect at Octopus Deploy, to discuss how taking a DevOps approach paves the way for staying ahead in business.

  • Webinar

    Meltdown and Spectre: what’s the impact on your SQL Server performance?

    Join Bob Pusateri, Microsoft Certified Master (MCM) and Solutions Architect at Heraflux Technologies, as he delves into the scary world of processor vulnerabilities, brought to light recently by the Meltdown and Spectre bugs. Bob will explore the impact these bugs can have on your estate, from initial vulnerability through to performance after patching, and how you can keep on top of it all with SQL Server monitoring.

  • Article

    SQL Change Automation with PowerShell Scripts: getting up-and-running

    Phil Factor demonstrates an automated way to verify a database build, from source control, deploy the source code and database documentation as a NuGet package, and use it to it to synchronize a target database.

  • University

    Take the SQL Toolbelt course

    SQL Toolbelt includes all of the components that enable Database DevOps. This step-by-step guide takes you through the process right from being able to analyze the impact of database changes through to making those changes, source controlling them, deploying them out to your target environments, and finally monitoring and documenting those environments.

    Here you will see an overview of each tool and how it can benefit your organization, but be sure to check in the module description whether there is a full course available for the tool you are learning.

  • Forums

    SQL Doc Forum

    Document SQL Server databases