24 April 2017
24 April 2017

How to document multiple SQL Server databases 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.

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 use the GUI, so what are your choices? Fortunately, by creating a SQL Doc XML Project file, you can specify anything that you can specify within the GUI. Supply the path to the file, as a command-line parameter, and you get both automation and a lot more flexibility, as I’ll demonstrate in this quick tip.

Hidden treasure in the SQL Doc Project file

SQL Doc works best when documenting one or more databases on a particular server, using a SQL Doc XML project file (.sqldoc). You use the tool in User Interface mode to create a project file that you then use, making slight modifications, when appropriate, from the command line.

For example, you can override some of the settings specified in the project file with command-line settings. This means you can run the same project file for several different servers, just by specifying the server and the databases on the server you want to document.

However, what if you want to name the documentation files differently for each database? There is no command line option for that.

In the command line, you can only control the way that objects such as tables are documented, allowing you to exclude creation scripts, triggers, indexes, foreign keys, check constraints or permissions. But what if you want to exclude other objects?

You can, in the project file, specify precisely what classes of object you want to exclude. You can exclude Tables, views, programmability objects (stored procedures, functions, database triggers, types, defaults and rules), and security objects (users, roles and schemas).

Passing in a project file on the command line

We can create or alter the Project XML file, and then pass the path to that file as a command-line parameter. We’ll take the easier, but slightly reckless approach of creating a project XML file, rather than altering an existing one. (It isn’t a ‘supported’ approach, however, so don’t send anyone indignant emails if it doesn’t work.)

This PowerShell script automates the documentation for four databases (Dave, Dee, Dozy, Beaky) on a single server. Obviously, you should change the values of the variables before you run this code.

Notice that for the $outputformat variable, we can specify several different types of documentation. I find the most useful form of documentation to be Framed HTML, which mimics the object browser in SSMS and provides a very quick way of navigating a database schema. If you just want output, the PDF option is useful, and Docx is also very good and has the advantage that it can quickly be converted into another format.

Sadly, I’ve never managed to get the Markdown format to do anything that can read within a markdown viewer. It seems to be a direct conversion of the HTML format, producing a vast number of individual markdown files, and so isn’t suitable for a document format.

To find out how to get started with SQL Doc and PowerShell, or how to create database documentation in ePub format 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

Misuse of the scalar user-defined function as a constant (PE017)

Do not use a scalar user-defined function (UDF) in a JOIN condition, WHERE search condition, or in a SELECT list, unless the function is schema-bound. Scalar UDFs are often used without a parameter to...

Also in Product learning

Customizing the SQL Prompt built-in snippets: a better 'ata' snippet

Snippets are a great feature of SQL Prompt. They save coding time, and introduce standards and consistency to the way you build code modules. They have multiple replacement points (placeholders) for p...

Also in SQL Doc

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

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