Use PowerShell to Document Your SQL Database

   

Summary: Learn how to use Windows PowerShell to document your Microsoft SQL Server database

 

Hey, Scripting Guy!  Is it possible to use Windows PowerShell to document my Microsoft SQL Server database?

— CR

 

Hello CR, Microsoft Scripting Guy Ed Wilson here. Welcome to day three of guest blogger week as it brings Rhys Campbell.

 

Rhys Campbell is a London based SQL Server & MySQL Database Administrator working for a statistics research company where he gets to put Windows PowerShell to many uses. Rhys blogs about SQL Server, MySQL, Windows PowerShell, SSIS and other stuff at the you did what with SQL webpage.

 

Using Windows PowerShell to generate database documentation

More often than not if database documentation exists, it is out of date or incomplete as it’s at the bottom of every ones to-do list. I’ve often advocated the use of extended properties to document databases.  These comments can be useful for others to gain a better understanding of your database.  You’ve probably added these to the Description field present in the SSMS table designer. By default Microsoft calls these extended properties MS_Description. See the following figure:

 

Unfortunately, these are not very useful when they just exist in the database. Using Windows PowerShell and SMO we can generate documentation by extracting this metadata from SQL Server databases and formatting it into something more useful.

The script presented here will generate html documentation for all user databases on a SQL Server instance. The database doesn’t have to have extended property comments but it will improve the documentation if they are present.  The script will extract these comments, along with a few other details, for tables, columns, functions, views, stored procedures and triggers.

First we need to load a couple of assembles so we are able to use SMO.

# Load needed assemblies

[System.Reflection.Assembly]::LoadWithPartialName(“Microsoft.SqlServer.SMO”) | Out-Null;

[System.Reflection.Assembly]::LoadWithPartialName(“Microsoft.SqlServer.SMOExtended”)| Out-Null;

 

This is followed by several functions that perform various tasks like writing html files, accessing the various database object types, reading extended properties and building lists of hyperlinks. There are comments above each function explaining their function. See the complete script at the Script Repository.

The next line set the directory where the documentation will be created. A folder called ‘database_documentation’ will be created in your user profile directory. This is C:\Users\Rhys on my laptop.

# Root directory where the html documentation will be generated

$filePath = “$env:USERPROFILE\database_documentation\”;

New-Item -Path $filePath -ItemType directory -Force | Out-Null;

 

Set the SQL Server instance you wish to document. Here I’m documenting an installation of SQL Express 2008 on localhost.

# sql server that hosts the databases we wish to document

$sql_server = New-Object Microsoft.SqlServer.Management.Smo.Server localhost\sqlexpress;

 

SMO doesn’t return the IsSystemObject property by default so we have to ask for it first. This property is used to avoid cluttering up the documentation with system databases, views and stored procedures.

# IsSystemObject not returned by default so ask SMO for it

$sql_server.SetDefaultInitFields([Microsoft.SqlServer.Management.SMO.Table], “IsSystemObject”);

$sql_server.SetDefaultInitFields([Microsoft.SqlServer.Management.SMO.View], “IsSystemObject”);

$sql_server.SetDefaultInitFields([Microsoft.SqlServer.Management.SMO.StoredProcedure], “IsSystemObject”);

$sql_server.SetDefaultInitFields([Microsoft.SqlServer.Management.SMO.Trigger], “IsSystemObject”);

 

A call to the getDatabases function will return all the user databases on the SQL Server instance.

# Get databases on our server

$databases = getDatabases $sql_server;

 

The foreach loop will cycle through each database generating documentation for each one. The functions defined earlier are used to document schemata, tables, views, stored procedures, function and triggers.

foreach ($db in $databases)

{

      Write-Host “Started documenting ” $db.Name;

      # Directory for each database to keep everything tidy

      New-Item -Path $($filePath + $db.Name) -ItemType directory -Force | Out-Null;

 

      # Make a page for the database

      $db_page = $filePath + $($db.Name) + “\index.html”;

      $body = “<ul>

                        <li><a href=’Schemata/index.html’>Schemata</a></li>

                        <li><a href=’Tables/index.html’>Tables</a></li>

                        <li><a href=’Views/index.html’>Views</a></li>

                        <li><a href=’Stored Procedures/index.html’>Stored Procedures</a></li>

                        <li><a href=’Functions/index.html’>Functions</a></li>

                        <li><a href=’Triggers/index.html’>Triggers</a></li>

                  </ul>”;

      writeHtmlPage $db $db $body $db_page;

           

      # Get schemata for the current database

      $schemata = getDatabaseSchemata $sql_server $db;

      createObjectTypePages “Schemata” $schemata $filePath $db;

      Write-Host “Documented schemata”;

      # Get tables for the current database

      $tables = getDatabaseTables $sql_server $db;

      createObjectTypePages “Tables” $tables $filePath $db;

      Write-Host “Documented tables”;

      # Get views for the current database

      $views = getDatabaseViews $sql_server $db;

      createObjectTypePages “Views” $views $filePath $db;

      Write-Host “Documented views”;

      # Get procs for the current database

      $procs = getDatabaseStoredProcedures $sql_server $db;

      createObjectTypePages “Stored Procedures” $procs $filePath $db;

      Write-Host “Documented stored procedures”;

      # Get functions for the current database

      $functions = getDatabaseFunctions $sql_server $db;

      createObjectTypePages “Functions” $functions $filePath $db;

      Write-Host “Documented functions”;

      # Get triggers for the current database

      $triggers = getDatabaseTriggers $sql_server $db;

      createObjectTypePages “Triggers” $triggers $filePath $db;

      Write-Host “Documented triggers”;

      Write-Host “Finished documenting ” $db.Name;

}

The full script can be found here at the Script Repository. Save the Windows PowerShell code to a script called ExtractDatabaseDocumentation.ps1 and sign it. We can then execute the script against the SQL Server instance defined in $sql_server.  The following figure shows running the script.

 

 

A folder of database documentation will be produced for each database. This will look similar to below.

The Index page is shown here in the following figure:

 

The Table details page is seen here in this figure:

 

 

I’ve omitted a few cool things to cut down on the size of this script for the sake of brevity. SMO is very broad so there’s plenty more you could include in this script if you wanted to.  If there’s something else you want to include in the documentation then a few minutes consulting the SMO page on MSDN should help you on your way.

 

CR, that is all there is to using Windows PowerShell to generate documentation for your SQL Server database. Thanks Rhys for appearing today and sharing your expertise. Guest blogger week will continue tomorrow when our guest will be Shannon Ma.

I invite you to follow me on Twitter or Facebook. If you have any questions, send email to me at scripter@microsoft.com or post them on the Official Scripting Guys Forum. See you tomorrow. Until then, peace.

 

Ed Wilson, Microsoft Scripting Guy