Comparing SQL Indexes with Powershell

A common challenge I’ve faced is managing many SQL Server Indexes across multiple databases in multiple environments. There’s often a need to not only check that Indexes exist, but that they match. For example does your Test environment have the same Indexes as your Dev environment.  Traditionally I’ve always tackled this using SQL Management Studio, but after awhile I looked for a way to perform this faster through Powershell scripting.

Comparing SQL Indexes with Powershell

Before going into the details of this script I wanted to point out that this is a script that utilizes the Powershell Central Config that I detailed in an earlier post. The function Get-DBConnection is part of the Include-Configuration.ps1 script, refer to my earlier article on how to implement that.

Script Parameters: The script accepts 3 required parameters, Environment1, Environment2, and the Table to be compared. A 4th optional parameter Detailed (-detailed) will give you more verbose output.

Get-Index-Table Function: This function takes a connection string as a parameter. It then uses that connection string to query SQL Server for the current indexes on the table specified in the script parameters. It returns a datatable containing the index details. If we used the -Detailed parameter it will open a grid displaying the datatable.

Comparison: The main functionality of the script uses the Get-Index-Table to get two datatables. Each containing the Index details for the 2 Environments specified as script parameters. We then check if one of the datatables is null and the other is not, this would indicate we’re missing Indexes in one location. If we have a non-null datatable for both databases, we compare them using the Compare-Object cmdlet. If differences are found we display the Environment and the Index name that is different.

This script may not solve all of your SQL Index problems but it is a handy tool when comparing Indexes across many environments. It could also be easily modified to run automatically and generate alerts or as part of a custom DSC Module.

param (
  [Parameter(Mandatory=$true)]
  [String]$Env1,
  [Parameter(Mandatory=$true)]
  [String]$Env2,
  [Parameter(Mandatory=$true)]
  [String]$tablename,
  [switch]$Detailed
)

Write-Host " "
Write-Host "                                 " -BackgroundColor DarkCyan
Write-Host "      Index Comparison           " -BackgroundColor DarkCyan
Write-Host "                                 " -BackgroundColor DarkCyan
Write-Host " "

### Include Scripts ###
Write-Host "Loading Included Scripts" -ForegroundColor Magenta

if (!(Test-Path "\\atlfile01\scripts\include-configuration.ps1"))
 {Write-Error "Include Script Not Found: \\atlfile01\scripts\include-configuration.ps1"; exit}
 . "\\atlfile01\scripts\include-configuration.ps1"
########################

function Get-Index-Table($connstr)
{
 $query = "SELECT TableName = t.name, IndexName = idx.name, `
 IndexId = idx.index_id, ColumnId = ic.index_column_id, `
 ColumnName = col.name, idx.*, ic.*, col.* FROM sys.indexes `
 idx INNER JOIN sys.index_columns ic ON `
 idx.object_id = ic.object_id and idx.index_id = ic.index_id `
 INNER JOIN sys.columns col ON ic.object_id = col.object_id `
 and ic.column_id = col.column_id INNER JOIN sys.tables t `
 ON idx.object_id = t.object_id WHERE t.is_ms_shipped = 0 `
 AND t.name = '$tablename'"

 if ($Detailed)
 { Write-Host "$query" }

 $connection = New-Object System.Data.SqlClient.SqlConnection
 $connection.ConnectionString = $connstr
 $connection.Open()
 $command = $connection.CreateCommand()
 $command.CommandText  = $query

 $result = $command.ExecuteReader()

 $table = new-object “System.Data.DataTable”
 $table.Load($result)

 if ($Detailed){
  $table | Out-GridView
 }

 $connection.Close()
 return $table
}

$connectionString1 = Get-DBConnection $Env1
$connectionString2 = Get-DBConnection $Env2

Write-Host "Comparing Indexes on $tablename"
Write-Host " "
Write-Host "Checking $connectionString1"
$dataset1 = Get-Index-Table $connectionString1
Write-Host " "
Write-Host "Checking $connectionString2"
$dataset2 = Get-Index-Table $connectionString2
Write-Host " "

if (($dataset1 -eq $null) -and ($dataset2 -eq $null))
{
	# Neither have Indexes
	Write-Host "Neither databases have Indexes on this table" -ForegroundColor Green
} elseif (($dataset1 -eq $null) -and  ($dataset2 -ne $null))
{
	Write-Host "$Env1 is missing Indexes on this table." -ForegroundColor Yellow
} elseif (($dataset1 -ne $null) -and  ($dataset2 -eq $null))
{
	Write-Host "$Env2 is missing Indexes on this table." -ForegroundColor Yellow
}else{

# Compare tables
$diff = Compare-Object $dataset1 $dataset2;
# Are there any differences?
if($diff -eq $null)
{ Write-Host "The Indexes are the same." -ForegroundColor Green ; }
else
{ Write-Host "The Indexes are different." -ForegroundColor Yellow;
  $diff | foreach  {
      if ($_.sideindicator -eq '<=') {$_.sideindicator = $Env2}

      if ($_.sideindicator -eq '=>') {$_.sideindicator = $Env1}
	  #Write-Host $_.InputObject["IndexName"]
	  $_.InputObject = $_.InputObject["IndexName"]
 }
 Write-Host "Missing Indexes"
 $diff | select @{l='Index';e={$_.InputObject}},@{l='Database';e={$_.SideIndicator}}
}
}
Write-Host " "
Write-Host "                                 " -BackgroundColor DarkCyan