<#
.SYNOPSIS
Script can be used to compare indexes on tables on different servers/databases.
.DESCRIPTION
When executed the script will check passed in databases on different (or the same) server and output a SQLCMD script which
can be executed to add missing indexes.
.PARAMETER <paramName>
-Server1 The source SQL Instance that is used as the master
-Server2 The destination SQL Instance that will be the target for additional indexes
-Database1 The source database
-Database2 The destination database (leave blank if the source and destination databases are the same)
-OutputScriptLocation Path and filename for the .sql file that will be created for any indexes that need to be added
-Verbose Output some logging information
.EXAMPLE
./Compare-Indexes -Server1 localhost -Server2 remotehostinstance1 -Database MyDB -OutputScriptLocation C:TempNewIndexes.sql -Verbose
#>
param
(
[string]$Server1 = "$(Read-Host 'SQL Instance 1 [e.g. (local)]')",
[string]$Server2 = "$(Read-Host 'SQL Instance 2 [e.g. (local)]')",
[string]$Database1 = "$(Read-Host 'Database Name')",
[string]$Database2,
[string]$OutputScriptLocation = "$(Read-Host 'Output File Name')",
[switch]$Verbose
)
if (!$Database2) { $Database2 = $Database1}
$MissingIndexCount = 0
try
{
#Initialize the output file
$FileInitiation = @"
:ON ERROR exit
:setvar SQLInstance `"$Server2`"
:setvar Database `"$Database2`"
:CONNECT `$(SQLInstance)
USE `$(Database);
"@
$FileInitiation | Out-File $OutputScriptLocation
$File = "True"
}
catch { Write-Host "Output folder does not exist...aborting script."
$File = "False"
}
if ($File -eq "True")
{
if ($Server1 -ilike "**") { $BaseServer1 = "SQLSERVER:SQL$Server1Databases$Database1Tables" }
else { $BaseServer1 = "SQLSERVER:SQL$Server1defaultDatabases$Database1Tables" }
if ($Server2 -ilike "**") { $BaseServer2 = "SQLSERVER:SQL$Server2Databases$Database2Tables" }
else { $BaseServer2 = "SQLSERVER:SQL$Server2defaultDatabases$Database2Tables" }
Write-Output "Please wait...comparing $Server1.$Database1 to $Server2.$Database2"
$Server1List = dir $BaseServer1 | select DisplayName
$Server2List = dir $BaseServer2 | select DisplayName
#For information purposes show a list of tables missing on server2 if verbose enabled
if ($Verbose)
{
$MissingTables = Compare-Object $Server1List $Server2List -Property DisplayName | Where-Object {$_.SideIndicator -eq '<=' } | Select DisplayName
foreach ($MsTbl in $MissingTables)
{ $MissingTable = $MsTbl.DisplayName
Write-Output "Missing table - $MissingTable"
}
}
#The compare uses -IncludeEqual as we only want to compare indexes on tables that exist on both servers
$TableList = Compare-Object $Server1List $Server2List -Property DisplayName -IncludeEqual | Where-Object {$_.SideIndicator -eq '==' } | Select DisplayName
if ($TableList)
{
foreach ($Table in $TableList)
{
$MissingIndex = "" #Initialize the variable so we can null it
Clear-Variable MissingIndex #Null the variable as we'll be checking for a value in here later
$TableToCheck = $Table.DisplayName
$Table1Index = dir $BaseServer1$TableToCheckIndexes | Select Name # | Where-Object {$_.DisplayName -eq "$TableToCheck"}).indexes | Select Name
$Table2Index = dir $BaseServer2$TableToCheckIndexes | Select Name # | Where-Object {$_.DisplayName -eq "$TableToCheck"}).indexes | Select Name
if ($Verbose) {Write-Host "Checking $TableToCheck"}
$MissingIndex = Compare-Object $Table1Index $Table2Index -Property Name -IncludeEqual | Where-Object {$_.SideIndicator -eq '<='} | Select Name
if ($MissingIndex)
{
$MissingIndexCount += 1
$Index = $MissingIndex.Name
Write-Output "Missing Index - $Index on $TableToCheck"
dir $BaseServer1$TableToCheckIndexes | where-object {$_.Name -eq "$Index"} |
% {$_.Script() | out-file $OutputScriptLocation -append; "; `r`nRAISERROR(`'Index $TableToCheck.$Index created...`',0,1) WITH NOWAIT; `r`n`r`n " |
out-file $OutputScriptLocation -Append;}
}
}
if ($MissingIndexCount -gt 0) {Write-Output "File $OutputScriptLocation created. Open this file in SSMS and execute in SQLCMD mode"}
else {Write-Output "No missing indexes found!"}
}
else { Write-Output "No matching tables found."}
}