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.
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.
|
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 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 |
# these Aliases 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 $MyProject = 'MyDatabases' $outputPath = "D:\Documentation\$MyProject" $projectPath = "$outputPath\$MyProject.sqldoc" $ReportFile = "$outputPath\outcome.txt" # We need to set these to the right server-instance and database for your particular task # the directory in which you want the files # the name of your server-instance $MyServer = 'MyServer\MyInstance' $ServerAlias = 'BobTheServer' $Databases = ('Dave', 'Dee', 'Dozy', 'Beaky') # the database. $header = 'My Databases' $ProjectName = 'MyDevelopmentProject' $TheDescription = 'This is just a demonstration.' $outputName = 'TheOutputName' #the subdirectory it is all put in $author = 'Phil Factor' $outputformat = 'WordDocx' #'Pdf','Markdown','StandardHTML','FramedHTML' #make sure that the directory exists, otherwise create it if (-not (Test-Path "$outputPath")) { New-Item -ItemType Directory -Path $outputPath } # SQL Doc works best with a project file # we now insert out variables into the XML file @" <?xml version="1.0" encoding="utf-16"?> <Project Version="2" ApproxVals="17" Server="$MyServer" Database="" IntegratedSecurity="True"> <Options> $($databases | foreach{ " <Option uri=`"http://sqldoc/project/selections/databases/user/[$_]/include/`" value=`"True`" /> " }) <Option uri="http://sqldoc/project/description/" value="$TheDescription" /> <Option uri="http://sqldoc/project/outputstyle/selected/" value="$outputformat" /> <Option uri="http://sqldoc/project/name/" value="$ProjectName" /> <Option uri="http://sqldoc/project/outputname/" value="$outputName" /> <Option uri="http://sqldoc/project/outputpath/" value="$outputPath" /> <Option uri="http://sqldoc/project/toc/populate/" value="True" /> <Option uri="http://sqldoc/project/cover/include/" value="True" /> <Option uri="http://sqldoc/project/server_alias/" value="$ServerAlias" /> <Option uri="http://sqldoc/project/outputname/addtimestamp/" value="False" /> <Option uri="http://sqldoc/project/footer/date/format/" value="dd MMMM yyyy HH:mm:ss" /> <Option uri="http://sqldoc/project/header/breadcrumbs/include/" value="True" /> <Option uri="http://sqldoc/project/header/text/" value="$header" /> <Option uri="http://sqldoc/project/footer/copyright/text/" value="Copyright 2017 - All Rights Reserved" /> <Option uri="http://sqldoc/project/author/" value="$author" /> <Option uri="http://sqldoc/project/outputstyle/word/pagesize" value="Letter" /> <Option uri="http://sqldoc/project/logo/" value="" /> </Options> </Project> "@ | Out-File $projectPath # execute SQL Doc sqldoc /project:$projectPath /force /out:$reportfile if ($?) # if there was no error { $VerbosePreference = 'Continue' write-verbose "$LASTEXITCODE $(Get-Content $reportFile -Raw)" } else #there was an error { #Hmm. An error code was passed back. Should we look at it? Write-warning "$LASTEXITCODE $(Get-Content $reportFile -Raw)" } $VerbosePreference = 'silentlyContinue' |
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 Toolbelt Essentials
The industry-standard tools for SQL Server development and deployment.
You may also like
-
Article
What is the SPACE developer productivity framework?
The SPACE framework is an acronym for an approach to measuring, understanding, and optimizing engineering productivity. Outlined by researchers from GitHub, Microsoft, and the University of Victoria, it encourages leaders to look at productivity holistically, placing leading metrics in context with each other and linking them to output, often team goals, rather than individual effort.
-
Webinar
Sechs Strategien zur Effizienzsteigerung Ihrer SQL Server-Datenbankentwicklung mit SQL Prompt und mehr
Einem aktuellen IDC-Bericht zufolge werden bis 2026 über 90% der Unternehmen weltweit die Auswirkungen des IT-Fachkräftemangels spüren. Dies könnte zu Verlusten von etwa 5,5 Billionen US-Dollar durch Produktverzögerungen, eingeschränkte Wettbewerbsfähigkeit und entgangene Geschäftschancen führen. Infolgedessen sehen sich Angestellte mit höheren Arbeitslasten konfrontiert, was zu geringerer Zufriedenheit bei Kunden und Mitarbeitenden führt. Eine Methode, um die
-
Article
SQL Prompt Safety Net Features for SSMS: Tab Coloring
The Tab Coloring component of SQL Prompt’s SSMS Tab management could save you from accidentally doing something apocalyptic on a production server, ever again.
-
Article
Testing SQL Server Databases Using SQL Test
During database development, as well as writing SQL, you must also write the tests that prove your SQL code meets all the requirements. The article will help you get started with database testing for your SQL Server databases, by creating and running unit tests against them, as well as static code analysis tests.
-
Forums
SQL Toolbelt Essentials Forum
Discussions on Redgate's SQL Server products including SQL Prompt, SQL Monitor and SQL Change Automation.
