Compare Indexes using Powershell

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.

 

cls
$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
		}
	}
}

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
	}
}

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

%d bloggers like this: