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.
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.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 |
# these aren't strictly necessary, but aliases make the subsequent code cleaner. You need to add # the path to where these executable files are stored. Set-Alias SQLDoc 'C:\Program Files (x86)\Red Gate\SQL Doc 3\SQLdoc.exe' -Scope Script Set-Alias pandoc 'C:\Program Files\RStudio\bin\pandoc\pandoc.exe' -Scope Script # We need to set these to the right server-instance and database for your particular task # the directory in which you want the files $outputDirectory = 'C:\Users\Phil.Factor\Documents\Databases\AdventureWorks' # the name of your server-instance $MyServer = 'MyServerName\MyInstanceName' # the database. $MyDatabase = 'AdventureWorks' #make sure that the directory exists, otherwise create it if (-not (Test-Path "$outputDirectory")) { New-Item -ItemType Directory -Path $outputDirectory } # execute SQL Doc sqldoc /server:$MyServer /database:$MyDatabase /filetype:docx /outputfolder:$outputDirectory /force /out:$outputDirectory\WhatHappened.txt if ($?) # if there was no error { #now find out the name of the output file and use that as input for Pandoc get-content "$outputDirectory\WhatHappened.txt" | foreach { #line by line if ($_ -match '(?im)Generated file "(?<Path>.{1,120})(?<Filetype>\..{1,10})"') { # if the line was 'Generated file "....."' Write-Host "writing out $($matches['Path']).epub" # this will be write-verbose pandoc -s "$($matches['Path'])$($matches['Filetype'])" -t Epub3 -o "$($matches['Path']).epub" if (Test-Path "$outputDirectory\$MyDatabase.epub") #if the file already exists { Rename-Item "$($matches['Path']).epub" "$($MyDatabase)$(Get-Date -format 'mHdMyy').epub" } else #Uh The file doesn't already exist { Rename-Item "$($matches['Path']).epub" "$MyDatabase.epub" } #just write the simple file } } } else #there was an error { #Hmm. An error code was passed back. Should we look at it? Write-Error $LASTEXITCODE } |
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 Toolbelt Essentials
The industry-standard tools for SQL Server development and deployment.