Day: December 13, 2011

Find duplicate indexes in Sql Server with Powershell

Here is a quick script to find duplicate indexes on tables in sql server using powershell.  You’ll need SqlPSX installed to run this.  The output is not pretty, as it just writes to the host, but it works.  I’ll leave it to you to write it to excel or whatnot.  The code to write to excel can be found in my previous post.

function CheckIndexColumns($table, $index)
{
	$columns = $index.IndexedColumns# | Sort-Object -Property Name
	foreach($idx in $table.Indexes | where{$_.Name -ne $index.Name})
	{
		#if($idx.IndexedColumns.Count -ne $index.IndexedColumns.Count){continue;}
		#$sourceCols = $index.IndexedColumns | Sort-Object -Property Name
		$targetCols = $idx.IndexedColumns #| Sort-Object -Property Name
		
		$diff = Compare-Object -ReferenceObject $columns -DifferenceObject $targetCols -SyncWindow 1000
		if($diff -eq $null)
		{
			Write-Host "Possible duplicate index found on table Table: $($table.Name)"  -ForegroundColor Red
			write-Host "Index [$($index.Name)] and index [$($idx.Name)] have the same columns:  $($idx.IndexedColumns)" -ForegroundColor DarkCyan
		}
	}
}

cls
$db = Get-SqlDatabase -sqlserver ServerName -dbname DatabaseName

foreach($table in $db.Tables)
{
	Write-Host "Checking table $($table.Name)..." -ForegroundColor Gray
	foreach($idx in $table.Indexes)
	{
		CheckIndexColumns $table $idx
	}
}