Scripting Indexes using Powershell and SMO

As part of a review of the indexes across my new workplace’s estate, I wanted to get all the indexes scripted up into re-runnable scripts which could then be loaded into source control.  However, considering the number of databases, tables and indexes, doing this manually would be both time consuming and mind numbingly boring; as such, I decided to create a powershell function to do this.

I’ll be honest, I’m still very much learning Powershell, and each time I decide to perform a task using it I end up learning plenty of new tricks.

The function takes the following parameters:-

  • $ServerName – the name of the SQL Server instance we’re connecting to
  • $DatabaseName – the name of the Database we want to script indexes from
  • $OutputPath – the root directory we want to output to; the server name, database name, schema name and table name will all be automatically populated as subdirectories under this directory
  • $TableList – an array of tables we wish to create indexes for; if left blank, the function will create indexes for all the tables in that database

The function also utilises the SMO scripter object.  Using the scripter object, we’re able to set some of the key requirements for the scripts to be re-runnable: a check for if the object exists, a drop statement, and to append the create statement to the end of the script we create for the drop.  Essentially, we have to create two scripter objects, one to drop the index, and a second to recreate it afterwards.  Ideally, I’d really like to be able to perform a check to see if the index already exists in the desired state with the same columns, included columns, fillfactor etc, but for the moment this drop and create script will suffice.

Here are the scripter.options settings we’re going to use:-

#set script options for the drop statement
$ScriptDropExisting = new-object ("Microsoft.SqlServer.Management.Smo.Scripter") $SQLServer
$ScriptDropExisting.Options.ScriptDrops = $True
$ScriptDropExisting.Options.IncludeIfNotExists = $True
$ScriptDropExisting.Options.ToFileOnly = $True

#set script options for the create statement
$ScriptCreate = new-object ("Microsoft.SqlServer.Management.Smo.Scripter") $SQLServer
$ScriptCreate.Options.IncludeIfNotExists = $True
$ScriptCreate.Options.ToFileOnly = $True
$ScriptCreate.Options.AppendToFile = $True

The crucial bit here is that we have to use two scripters; $ScriptDropExisting is the scripter object for the drop index statement; this is controlled through the scripter Options.ScriptDrops property. $scriptCreate is the scripter object for create index scripts, this uses the scripter Options.AppendToFile property so we can combine the two scripts into one file that first drops the existing index, then creates the new index.

The next bit that caused some issue was attempting to cater for both a list of tables to be passed in, and if that parameter was left blank running against all tables on the database. In order to get the smo object for the table, we need to specify the schema name and table name in the following manner to the databases class in smo:- databases.tables[tablename, schemaname]. However, when populating the list of tables from the databases.tables class into an object, the table name is displayed as [schemaname].[tablename]. If we want to validate a tablelist that is passed in, we will need to populate this with the list of tables in the same format, then use the Compare-Object cmdlet to compare the two objects. If we set a new object to be the results where the SideIndicator property equals “==”, then we can pass this through to the loop to get the index script. This means that we need to populate an object with all the tables in the database in the format [schemaname].[tablename], and then decide whether we are comparing that object to the list passed in or not. After this, we need to strip out the schema and table names in order to create the smo object for the table that we can use to generate our index scripts from. Basically, some hoop jumping!

The code to create these objects, and then pull them apart is below:-

#set the tables we want to generate scripts for; if $TableList is null then get all tables, otherwise use the list provided
#First populate our table list from the actual server

$TableList2 = @()
[string[]]$TableList2 += $SQLServer.Databases[$DatabaseName].Tables

#if $TableList is null, then set $Tables to the above list of tables in SQL, otherwise check for the tables we've passed in

if ($TableList -eq $null)
{
    #set $Tables = $TableList2
    $Tables = $TableList2
}
else
{
    #Compare this list to the table names we passed in
    $Tables = Compare-Object $TableList $TableList2 -IncludeEqual -PassThru | where-object {$_.SideIndicator -eq "=="}
}

In my environment, the function took around 5 minutes to create scripts for some 1,800 indexes in a database; so overall I’m happy with the results. I can’t imagine how long it would take to manually go through and script those indexes out!

The full function is below:

# Function to script out indexes on a specified database and table list
# If table list is left blank, all tables will be scripted.
# If a table list is provided, this needs to be provide in the following format:- [schemaname].[tablename]

function Export-IndexScripts
{
    [cmdletbinding()]
    Param([String]$ServerName
    , [String]$DatabaseName
    , [string]$OutputPath
    , [String[]]$TableList)

    #verbose message to show the server object
    $Message = [string](get-date) + ": Creating smo object for $ServerName"
    write-verbose $Message

    # create the smo object of the server
    $SQLServer = New-Object ('Microsoft.SqlServer.Management.Smo.Server') $ServerName

    # set the generic output path with the server name
    $OutputPath = $OutputPath + [string]$SqlServer.Name + "\"

    #verbose message to display the output path
    $Message = [string](get-date) + ": Output path set to $OutputPath"
    write-verbose $Message

    #set script options for the drop statement
    $ScriptDropExisting = new-object ("Microsoft.SqlServer.Management.Smo.Scripter") $SQLServer
    $ScriptDropExisting.Options.ScriptDrops = $True
    $ScriptDropExisting.Options.IncludeIfNotExists = $True
    $ScriptDropExisting.Options.ToFileOnly = $True

    #set script options for the create statement
    $ScriptCreate = new-object ("Microsoft.SqlServer.Management.Smo.Scripter") $SQLServer
    $ScriptCreate.Options.IncludeIfNotExists = $True
    $ScriptCreate.Options.ToFileOnly = $True
    $ScriptCreate.Options.AppendToFile = $True

    # get the database object
    $Database = $SQLServer.Databases.Item($DatabaseName)

    #add the database to the output path
    $OutputPath = $OutputPath + [string]$Database.Name + "\"

    #verbose message to display the output path
    $Message = [string](get-date) + ": Output path set to $OutputPath"
    write-verbose $Message

    #set the tables we want to generate scripts for; if $TableList is null then get all tables, otherwise use the list provided
    #First populate our table list from the actual server

    $TableList2 = @()
    [string[]]$TableList2 += $SQLServer.Databases[$DatabaseName].Tables

    #if $TableList is null, then set $Tables to the above list of tables in SQL, otherwise check for the tables we've passed in

    if ($TableList -eq $null)
    {
        #set $Tables = $TableList2
        $Tables = $TableList2
    }
    else
    {
        #Compare this list to the table names we passed in
        $Tables = Compare-Object $TableList $TableList2 -IncludeEqual -PassThru | where-object {$_.SideIndicator -eq "=="}
    }

    #loop through each table in the database
    ForEach($FullTableName in $Tables)
    {
        #Sort out getting the table back to being a string
        #find the location of the "." and use that as the start of a substring
        $TableName = $FullTableName.Substring(($FullTableName.IndexOf("."))+1)

        #replace the square brackets
        $TableName = $TableName.Replace('[', '')
        $TableName = $TableName.Replace(']', '')

        #get the schema name in the same manner as above
        $SchemaName = $FullTableName.Substring(0, $FullTableName.IndexOf(".")-1)
        $SchemaName = $SChemaName.Replace('[','')
        $SChemaName = $SchemaName.Replace(']','')

        #Set the table to be the smo object for that table
        $Table = $SQLServer.Databases[$DatabaseName].Tables[$TableName, $SchemaName]

        #get the indexes on the table
        $Objects = $Table.Indexes
        if($Objects -ne $null)
        {

            #verbose message to indicate which table we're scripting
            $Message = [string](get-date) + ": Scripting index for table :" + [string]$FullTableName
            Write-Verbose $message

            #set the output path
            $ScriptPath = $OutputPath + [string]$SchemaName + "\" + [string]$TableName

            #check the output path exists
            if ((test-path $ScriptPath) -eq $false)
            {
                $Message = [string](get-date) + ": Creating directory :" + [string]$ScriptPath
                Write-Verbose $Message

                #create the directory if doesn't exists
                New-item -path $ScriptPath -type directory | out-null
            }

            #loop through each index
            ForEach($Object in $Objects)
            {

                #set the output script file name based on the string we created earlier plus the name of the index we're scripting
                $ScriptFileName = $ScriptPath + "\" + [string]$Object.Name.replace(":", "_") + ".sql"
                $ScriptFileName = $ScriptFileName.Replace("<", "")

                #set the filename in the scripter object
                $ScriptDropExisting.Options.Filename = $ScriptFileName
                $ScriptCreate.Options.Filename = $ScriptFileName

                #Comfort message
                $Message = [string](get-date) + ": Generating index script for index " + [string]$Object.Name
                Write-Verbose $Message

                #call the two scripters
                $ScriptDropExisting.Script($Object)
                $ScriptCreate.Script($Object)
            }
        }
        else
        {
            #verbose message to indicate no indexes on this table
            $Message = [string](get-date) + ": No indexes exist for table :" + [string]$FullTableName
            Write-Verbose $message
        }
    }
}
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s