Documenting SQL Server Jobs Using Powershell

The following script can be used to create wiki style documentation for all SQL server jobs.

# 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")

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


##############################################################
# 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)
}

# create database directories, $doc-root
new-item -path $doc_root -name $project_name -type directory -force > $null
$file_name = $doc_root + "\" + $project_name + "\" + $project_name + " jobs" + ".wiki"
new-item $file_name -type file -force

add-content $file_name " || Job || Description || Created || Updated || Enabled || Schedules || "

foreach ($job in $server.jobserver.jobs)
{
$content = " | " + $job.name + " | " `
+ $job.description + " | " `
+ $job.datecreated + " | " `
+ $job.DateLastModified + " | " `
+ $job.IsEnabled+ " | "


foreach($schedule in $job.JobSchedules)
{
$content = $content + $schedule.FrequencyTypes

if($schedule.FrequencySubDayTypes -ne "once")
{
$content = $content + " every " `
+ $schedule.FrequencySubDayInterval + " " `
+ $schedule.FrequencySubDayTypes
}
}
$content = $content + " | "
add-content $file_name $content

}