24 April 2017
24 April 2017

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

Phil shows how to use SQL Doc, PowerShell and a SQL Doc XML Project file to document multiple databases at once.

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

You may also like

  • Webinar

    How to implement database DevOps at scale in 5 steps

    This webinar discusses the current state of database DevOps, the key challenges teams face, and the 5 steps you can take to scale database DevOps across your team and organization.

  • Community event

    SQLSaturday Johannesburg

    SQLSaturday is a training event for SQL Server professionals and those wanting to learn about SQL Server. Admittance to this event is free, all costs are covered by donations and sponsorship. Please register soon as seating is limited, and let friends and colleagues know about the event. Redgate will be there as one of the

  • Article

    How to deploy changes to Azure SQL Database using SQL Compare and Azure Active Directory

    Describing SQL Compare's built-in Active Directory Authentication mode, which makes it easier and more secure to, for example, deploy schema changes from version control to an Azure SQL Database.

  • Article

    Remembering passwords in SQL Compare and SQL Data Compare

    SQL Compare or SQL Data Compare can automatically populate your SQL Server credentials, if you wish. Just check the "Remember credentials" box, and passwords will now be stored in two places: the Windows Credential Store and your project file, if you save one.

  • 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