Unfortunately, this doesn’t compare the columns included in the index, just the indexes by name.  I’ll bang one together that does that latter.  You’ll need sqlpsx in order to run this.


$srcTables = Get-SqlTable $(Get-SqlDatabase -sqlserver "SourceServer" -dbname "SrcDB")
$targetTables = Get-SqlTable $(Get-SqlDatabase -sqlserver "TargetServer" -dbname "TargetDB")

foreach($table in $srcTables)
	$targetTable = $targetTables | where{$_.Name -eq $table.Name}
	$targetIDX = $targetTable.Indexes;
	$srcIDX = $table.Indexes;
	$diff = Compare-Object -ReferenceObject $srcIDX -DifferenceObject $targetIDX -Property Name
	if($diff -ne $null)
		Write-Host $diff


This script does compare the index columns, but I haven’t put the two together yet.  I need to clean up my scripts.  Winking smile

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

$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

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 )

Google photo

You are commenting using your Google 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 )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.