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.


$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

Compare tables with Powershell

Posting this for Michelle Uffort (@sqlfool).

$saUser = 'sa'
$saPwd = get-content "C:\Powershell\Monitoring\sacred.txt" | convertto-securestring 

$targetSrvCn = 'TargetServer'
$srcSrvCn = 'SourceServer'
$srcDBName = 'srcDB'
$targetDBName = 'TargetDB'

$targetConn = New-Object ('Microsoft.SqlServer.Management.Common.ServerConnection') ('TargetServer', $saUser, $saPwd)
$targetServer = New-Object ('Microsoft.SqlServer.Management.Smo.Server') $targetConn
$targetDB = $targetServer.Databases | where {$_.Name -eq $targetDBName}
$targetTables = $targetDB.Tables | where{$_.Name -notlike "*QueueCacheData*"}

$sourceConn = New-Object ('Microsoft.SqlServer.Management.Common.ServerConnection') ('SourceServer', $saUser, $saPwd)
$sourceServer = New-Object ('Microsoft.SqlServer.Management.Smo.Server') $sourceConn
$sourceDB = $sourceServer.Databases | where {$_.Name -eq $srcDBName}
$sourceTables = $sourceDB.Tables | where{$_.Name -notlike "*QueueCacheData*"}

$diff = Compare-Object -DifferenceObject $targetTables -ReferenceObject $sourceTables -SyncWindow 300 -Property Name

foreach($table in $sourceTables)
	$targetTable = $targetTables | where{$_.Name -eq $table.Name}
	if($targetTable -eq $null)
		Write-Host "Table $($table.Name) does not exist in target database." -ForegroundColor Red
	write-host $table.Name $targetTable.Name
	$targetColumns = $targetTable.Columns | sort $_.Name
	$srcColumns = $table.Columns | sort $_.Name
	$diff = Compare-Object -ReferenceObject $srcColumns -DifferenceObject $targetColumns -Property Name -SyncWindow 100

Get Sql Server Error Number from Exception

In order to get the error number from a, you’ll need to iterate through all the inner exceptions of the current $_ exception in your catch{} block:

	Get-SqlData -sqlserver "YourServerName" -dbname master -qry "SELECT 0/0"
	for($ex = $_.Exception; $ex -ne $null; $ex = $ex.InnerException)
		$ex | fl -Force

This will iterate through all the inner exceptions until there are no more.  I’ll leave it to you when to stop iterating through the inner exceptions.  In this case, the exception we are looking for is nested 4 innerexceptions in:

Errors         : {System.Data.SqlClient.SqlError: Divide by zero error encountered.}
Class          : 16
LineNumber     : 1
Number         : 8134
Procedure      : 
Server         : YourServerName
State          : 1
Source         : .Net SqlClient Data Provider
ErrorCode      : -2146232060
Message        : Divide by zero error encountered.
Data           : {HelpLink.ProdName, HelpLink.ProdVer, HelpLink.EvtSrc, HelpLink.EvtID...}
InnerException : 
TargetSite     : System.Object ExecuteTSql(ExecuteTSqlAction, System.Object, System.Data.DataSet, Boolean)
StackTrace     :    at Microsoft.SqlServer.Management.Common.ConnectionManager.ExecuteTSql(ExecuteTSqlAction action, Object execObject, DataSet fillDataSet, Boolean catchException)
                    at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteWithResults(String sqlCommand)

Edit: 20130103: You could also use $_.Exception.GetBaseException() to retrieve the first exception as well.