Welcome to Day 27 of my “A Month of PowerShell” series. This series will use the series landing page on this blog at http://blog.waynesheffield.com/wayne/a-month-of-powershell/. Please refer to this page to see all of the posts in this series, and to quickly go to them.
One of the tasks that we are frequently called upon to perform is to script out objects from one server and to apply them to another. Fortunately, PowerShell makes this easy.
Virtually every object in the SMO library has a Script method. Actually, they have two, one without parameters and one that accepts a ScriptOptions collection for how to control the scripting. Want to script out an entire database? Navigate to the database, and call the Script() method. Need a procedure? Navigate to the procedure, and call the Script() method. Views? Functions? Tables? Indexes? They all have a Script() method that you can use.
What’s really neat is that with every version of SQL Server, the SMO libraries are updated to handle the new features. For instance, SQL Server 2012 introduced a ColumnStore index. The Script() method for the index can script it out.
Have you ever written a T-SQL script to script out all of the indexes in a database? It can get quite long (the incomplete version that I have is over 300 lines of code)… and are you certain that it handles everything correctly? Here’s my PowerShell script that will script out any index. Over half of this script is parameters, comments and code to verify parameters and to get it ready to send the output to a file. The actual scripting of all of the indexes is less than 40 lines… let’s see you do that in T-SQL. As an added bonus, the next version of SQL Server will probably include clustered ColumnStore indexes… this script will generate those without any modifications, since the logic to generate the script is in the SMO library, not in PowerShell (the only thing that I might have to change is the method of ensuring that the SMO libraries are loaded… that keeps changing from version to version). Here is my PowerShell script:
#
# Syntax: & GetIndexes.ps1 "SQLServer\Instance" "Database" "OutputPathAndFileName"
#
# In order for PowerShell to run scripts, run Set-ExecutionPolicy RemoteSigned
#
# Notes:
# 1. If the specified output file already exists, it is deleted without warning.
# 2. If the specified output path does not exist, it is created.
#---------------------------------------------------------------------------------------------------
# PARAMETERS (Required)
#---------------------------------------------------------------------------------------------------
# $SQLInstance [string] - the name of the sql instance, in server\instance format.
# $Database [string] - the name of the database to script out indexes for.
# $FileName [string] - the location of the output file.
#---------------------------------------------------------------------------------------------------
# PARAMETERS (Optional)
#---------------------------------------------------------------------------------------------------
# $Schema [string] - the name of the schema to script out indexes for. If not specified, uses all.
# $Table [string] - the name of the table to script out indexes for. If not specified, uses all.
# $IncludeDrop [boolean] - set to true (default) to have the script generate drop statements and
# existance check prior to dropping.
# $AppendToFile [boolean] - set to true (default) to append to the file. False will overwrite the file.
# $IncludeHeaders [boolean] - set to true (default) to include scripting comments.
# $NoFileGroup [boolean] - set to true (default) to suppress filegroup placement options in generated script.
# $ToFileOnly [boolean] - set to true (default) to send generated script to the file only.
# Setting to false will also send the generated script to the screen.
#---------------------------------------------------------------------------------------------------
# MODIFICATION LOG
#---------------------------------------------------------------------------------------------------
#2012-08-31 WGS Initial Creation.
#---------------------------------------------------------------------------------------------------
param(
[String] $SQLInstance, # the SQL instance to generate a script from, in Server\Instance format.
[String] $Database, # the database to generate the index scripts from.
[String] $Filename, # the output file for the generated scripts.
#Optional parameters follow
[String] $SchemaName, # if specified, restricts generated scripts to tables in this schema.
[String] $TableName, # if specified, restricts generated scripts to tables with this name.
[boolean] $IncludeDrop = $True, # set to $True to include drop statements
[boolean] $AppendToFile = $True, # set to $False to overwrite file
[boolean] $IncludeHeaders = $True, # set to $False to not have the scripting headers
[boolean] $NoFileGroup = $True, # set to $False to include filegroup placement in script
[boolean] $ToFileOnly = $True # set to $False to send results to screen also
)
cls
if ((!$SQLInstance) -or (!$Database) -or (!$Filename))
{
Write-Warning 'Syntax: & "GetIndexes.ps1" "SQLServer\Instance" "DatabaseName" "Output Path and Filename" '
Write-Warning 'Syntax (Optional Parameters): '
Write-Warning ' "SchemaName"'
Write-Warning ' "TableName"'
Write-Warning ' "Include Drop Statements" (boolean)'
Write-Warning ' "Append each object''s script to file" (boolean)'
Write-Warning ' "Include Script Headers" (boolean)'
Write-Warning ' "Don''t include filegroup placement settings" (boolean)'
Write-Warning ' "Send script output to file only" (boolean)'
Write-Warning ' "Pass in boolean values as $True or $False"'
Write-Host ""
}
else
{
# if the file already exists, delete it
if (Test-Path $Filename) {Remove-Item $Filename}
# get the path that this file is in
$Path = Split-Path $Filename -Parent
# if the path doesn't exist, create it
if (!(Test-Path -path $Path)) {New-Item $Path -Type Directory}
Write-Host "Output file: $Filename"
#Load SMO, connect to server and database
[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null
$server = New-Object ('Microsoft.SqlServer.Management.SMO.Server') "$SQLInstance"
$db = $server.Databases[$Database]
# this object is used to script the actual index
$Scriptr = New-Object ('Microsoft.SqlServer.Management.Smo.Scripter') ($server)
$Scriptr.Options.ScriptDrops = $False
$Scriptr.Options.Indexes = $True
$Scriptr.Options.IncludeHeaders = $IncludeHeaders
$Scriptr.Options.ClusteredIndexes = $True
$Scriptr.Options.NonClusteredIndexes = $True
$Scriptr.Options.XmlIndexes = $True
$Scriptr.Options.AppendToFile = $AppendToFile
$Scriptr.Options.FileName = $Filename
$Scriptr.Options.ToFileOnly = $ToFileOnly
$Scriptr.Options.NoFileGroup = $NoFileGroup
foreach ($table in $db.Tables)
{
if (((!$TableName) -or ($TableName -eq $table.name)) -and
((!$SchemaName) -or ($SchemaName -eq $table.Schema)))
{
foreach ($index in $table.Indexes)
{
Write-Host "Database: $db; Table: $table; Index: $index"
if ($IncludeDrop -eq $True)
{
$Scriptr.Options.IncludeIfNotExists = $True
$Scriptr.Options.ScriptDrops = $True
$Scriptr.Script($index)
}
$Scriptr.Options.IncludeIfNotExists = $False
$Scriptr.Options.ScriptDrops = $False
$Scriptr.Script($index)
}
}
}
}
