Creating Database Documentation with Powershell

Creating database documentation can be time consuming and difficult to maintain. Have you ever tried to persuade developers or DBAs of the benefits of documentation. They are quick to complain when there is no documentation on a system they are forced to maintain.

The following powershell script provides a simple method of creating documentation for database objects that can then be uploaded to your wiki.

1. Load the required SMO libraries

#Load SMO libraries
[Reflection.Assembly]::Load("Microsoft.SqlServer.Smo, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91")
[Reflection.Assembly]::Load("Microsoft.SqlServer.SqlEnum, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91")
[Reflection.Assembly]::Load("Microsoft.SqlServer.SmoEnum, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91")
[Reflection.Assembly]::Load("Microsoft.SqlServer.ConnectionInfo, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91")

2. Define database connection properties

# define connection properties
[string] $server_name = '(local)'
[string] $db_name = 'master'
[string] $project_name = "MyProject"
[string] $doc_root = "C:\dba\db_docs\"
[bool] $windows_login = $false

3. I use winscp to publish the completed documentation to the wiki. The following function specifies the command line for this.

function ftp-files
{

. "C:\Program Files\WinSCP3\winscp3.exe" /console /command "option batch on" "option confirm off" "open myProfile" "put C:\dba\db_docs\$project_name\*.* /wiki/db_doc/$project_name/" "close" "exit"

}

4. This function creates the top level database page. It can be extended to add any required database or server details.

##############################################################
# doc-database
##############################################################
function doc-database
{
param($db_dir)

$file_name = $db_dir + "\" + $project_name + " Database " + ".wiki"
new-item $file_name -type file -force

add-content $file_name "h1. Overview"
add-content $file_name "{excerpt}...{excerpt}"

add-content $file_name "h1. Environment"
add-content $file_name "|| Designation || Server || Database || OS || SQL ||"

$string = "| Development | | " + $db_name + " | | |"
add-content $file_name $string
add-content $file_name "| Beta | | | | |"
add-content $file_name "| Live | | | | |"

add-content $file_name "h1. Database Objects"

$string = "h2. [Tables|" + $project_name + " Tables]"
add-content $file_name $string
$string = "{children:depth=2|excerpt=true|page=" + $project_name + " Tables}"
add-content $file_name $string

$string = "h2. [Views|" + $project_name + " Views]"
add-content $file_name $string
$string = "{children:depth=2|excerpt=true|page=" + $project_name + " Views}"
add-content $file_name $string

$string = "h2. [Stored Procedures|" + $project_name + " Stored Procedures]"
add-content $file_name $string
$string = "{children:depth=2|excerpt=true|page=" + $project_name + " Stored Procedures}"
add-content $file_name $string

$string = "h2. [Functions|" + $project_name + " Functions]"
add-content $file_name $string
$string = "{children:depth=2|excerpt=true|page=" + $project_name + " Functions}"
add-content $file_name $string

$string = "h1. [Update Process|" + $project_name + " Update Process]"
add-content $file_name $string

add-content $file_name "h1. Attachments"
add-content $file_name "{attachments}"
}

5. fucntion to extract the extended properties from stored procedure parameters.

##############################################################
# doc-params
##############################################################
function doc-params
{
param($file_name, $parameters)

add-content $file_name "|| Parameter || Data Type || Default || Description ||"
foreach ($param in $parameters)
{
$string = "| " + $param.name + " | " + $param.datatype
if ($param.datatype.name -eq 'varchar')
{
$string = $string + "(" + $param.datatype.maximumlength + ")"
}
$string = $string + " | " + $param.default
$string = $string + " | "
$string = $string + $param.ExtendedProperties["MS_Description"].value
$string = $string + " | "

add-content $file_name $string
}
}

6. Fucntion to document all indexes.

##############################################################
# doc-indexes
##############################################################
function doc-indexes
{
param($file_name, $table)

add-content $file_name 'h2. Indexes'
add-content $file_name '|| Key Name || Columns || Included || Clustered ||'

foreach($index in $table.indexes)
{
$col_list = ""
$inc_list = ""

foreach($col in $index.indexedcolumns)
{
if($col.isincluded -eq "true")
{
$inc_list = $inc_list + $col.name + " "
}
else
{
$col_list = $col_list + $col.name + " "
}
}
$string = "| "+ $index.name + "| " + $col_list +"| " + $inc_list + "| " + $index.isclustered + "|"
add-content $file_name $string
}
}

7. Function to document a table.

##############################################################
# doc-table
##############################################################
function doc-table
{
param($table_dir, $table)

$file_name = $table_dir+ "\" + $project_name + " Table - " + $table.name + ".wiki"
new-item $file_name -type file -force

add-content $file_name "h1. $($table.name) "

add-content $file_name "{excerpt}"
add-content $file_name $table.ExtendedProperties["MS_Description"].value
add-content $file_name "{excerpt}"

add-content $file_name '|| Field || Type || Nullable || Key || Default || Description ||'

foreach($column in $table.columns)
{
$column_text = "|" + $column.name + "|" + $column.datatype

if ($column.datatype.name -eq 'varchar')
{
$column_text = $column_text + "(" + $column.datatype.maximumlength + ")"
}

$column_text = $column_text + " | " + $column.nullable + "|" + $column.inprimarykey `
+ "|" + $column.default + " | " `
+ $column.ExtendedProperties["MS_Description"].value `
+ " |"

add-content $file_name $column_text
}
doc-indexes $file_name $table
}

8. Fucntion to document a view.

##############################################################
# doc-view
##############################################################
function doc-view
{
param($view_dir, $view)

$file_name = $view_dir+ "\" + $project_name + " View - " + $view.name + ".wiki"
new-item $file_name -type file -force

add-content $file_name "h1. $($view.name) "
add-content $file_name "{excerpt}"
add-content $file_name $view.ExtendedProperties["MS_Description"].value
add-content $file_name "{excerpt}"

add-content $file_name '|| Field || Type || Description ||'

foreach($column in $view.columns)
{
$column_text = "|" + $column.name + "|" + $column.datatype

if ($column.datatype.name -eq 'varchar')
{
$column_text = $column_text + "(" + $column.datatype.maximumlength + ")"
}

$column_text = $column_text + " | " `
+ $column.ExtendedProperties["MS_Description"].value `
+ " |"
add-content $file_name $column_text
}

add-content $file_name "{code}"
add-content $file_name $view.TextHeader
add-content $file_name $view.TextBody
add-content $file_name "{code}"
}

9. Fucntion to document a stored procedure. When creating stored procedures I use a standard template and add extended properties. Both of which are used when constructing the documentation page.

##############################################################
# doc-procedure
##############################################################
function doc-procedure
{
param($proc_dir, $proc)

$file_name = $proc_dir + "\" + $project_name + " Stored Procedure - " + $proc.name + ".wiki"
new-item $file_name -type file -force

# add description as excerpt
add-content $file_name "h1. $($proc.name) "
add-content $file_name "{excerpt}"
add-content $file_name $proc.ExtendedProperties["MS_Description"].value
add-content $file_name "{excerpt}"

# add parameters
add-content $file_name "h1. Parameters"
doc-params $file_name $proc.parameters

# return value
add-content $file_name "h1. Return Value"
add-content $file_name $proc.ExtendedProperties["RETURN_VALUE"].value

# result set
add-content $file_name "h1. Result Set"
add-content $file_name $proc.ExtendedProperties["RESULT_SET"].value

# add the code
add-content $file_name "h1. Syntax"
add-content $file_name "{code}"
if($proc.IsEncrypted -eq $false)
{
if ($proc.TextHeader.IndexOf("Description:") -gt -1 `
-and $proc.TextHeader.IndexOf("Parameters:") -gt -1 `
-and $proc.TextHeader.IndexOf("Parameters:", $proc.TextHeader.IndexOf("Description:")) -gt -1 `
)
{
add-content $file_name $proc.TextHeader.substring($proc.TextHeader.ToUpper().IndexOf("CREATE PROCEDURE") `
,$proc.TextHeader.length - $proc.TextHeader.ToUpper().IndexOf("CREATE PROCEDURE")).trim().trimend("AS").trimend("as")
}
else
{
add-content $file_name $proc.TextHeader.trim().trimend("AS").trimend("as")
}
}
else
{
add-content $file_name "Procedure Encrypted"
}
add-content $file_name "{code}"

}

10. Fucntion to document a function.

##############################################################
# doc-function
##############################################################
function doc-function
{
param($func_dir, $func)

$file_name = $func_dir + "\" + $project_name + " Function - " + $func.name + ".wiki"
new-item $file_name -type file -force

# add description as excerpt
add-content $file_name "h1. $($func.name)"
add-content $file_name "{excerpt}"
add-content $file_name $proc.ExtendedProperties["MS_Description"].value
add-content $file_name "{excerpt}"

# add parameters
add-content $file_name "h1. Parameters"
doc-params $file_name $func.parameters

# return value
add-content $file_name "h1. Return Value"
add-content $file_name $proc.ExtendedProperties["RETURN_VALUE"].value

# add the code
add-content $file_name "h1. Syntax"
add-content $file_name "{code}"
if($func.IsEncrypted -eq $false)
{
if ($func.TextHeader.IndexOf("Description:") -gt -1 `
-and $func.TextHeader.IndexOf("Parameters:") -gt -1 `
-and $func.TextHeader.IndexOf("Parameters:", $func.TextHeader.IndexOf("Description:")) -gt -1 `
)
{
add-content $file_name $func.TextHeader.substring($func.TextHeader.ToUpper().IndexOf("CREATE FUNCTION") `
,$func.TextHeader.length - $func.TextHeader.ToUpper().IndexOf("CREATE FUNCTION")).trim().trimend("AS").trimend("as")
}
else
{
add-content $file_name $func.TextHeader.trim().trimend("AS").trimend("as")
}
}
else
{
add-content $file_name "Function Encrypted"
}
add-content $file_name "{code}"

}

11. and this tiee it all together.

##############################################################
# Main
##############################################################

# connect to server
$server = new-object ("Microsoft.SqlServer.Management.Smo.Server") $server_name

if ($windows_login -eq $false)
{
# prompt for connection details
$server.ConnectionContext.LoginSecure=$false;
$credential = Get-Credential
$username = $credential.UserName -replace("\\","")
$server.ConnectionContext.set_Login($username)
$server.ConnectionContext.set_SecurePassword($credential.Password)
}

$db = $server.Databases[$db_name]

# create database directories, $doc-root
new-item -path $doc_root -name $project_name -type directory -force

$db_dir = $doc_root + $project_name
$table_dir = $db_dir + "\Tables"
$view_dir = $db_dir + "\Views"
$proc_dir = $db_dir + "\Stored Procedures"
$func_dir = $db_dir + "\Functions"

new-item $table_dir -type directory -force
new-item $proc_dir -type directory -force
new-item $func_dir -type directory -force

doc-database $db_dir

####################################
# document all tables
####################################
$file_name = $table_dir + "\" + $project_name + " Tables" + ".wiki"
new-item $file_name -type file -force
add-content $file_name "{children:excerpt=true|depth=3}"
foreach ($table in $db.Tables)
{
doc-table $table_dir $table
}

####################################
# document all views
####################################
$file_name = $view_dir + "\" + $project_name + " Views" + ".wiki"
new-item $file_name -type file -force
add-content $file_name "{children:excerpt=true|depth=3}"
foreach ($view in $db.Views)
{
if($view.IsSystemObject -ne "true")
{
doc-view $view_dir $view
}
}


####################################
# document all stored procedures
####################################
$file_name = $proc_dir + "\" + $project_name + " Stored Procedures" + ".wiki"
new-item $file_name -type file -force
add-content $file_name "{children:excerpt=true|depth=3}"
foreach ($proc in $db.StoredProcedures)
{
if($proc.IsSystemObject -ne "true")
{
doc-procedure $proc_dir $proc
}
}

####################################
# document all functions
####################################
$file_name = $func_dir + "\" + $project_name + " Functions" + ".wiki"
new-item $file_name -type file -force
add-content $file_name "{children:excerpt=true|depth=3}"
foreach ($func in $db.UserDefinedFunctions)
{
if($func.IsSystemObject -ne "true")
{
doc-function $func_dir $func
}
}

12. If you want to make use of winscp to copy files to the wiki then make a cal lto the ftp-files function.

ftp-files