One of the handy things about Powershell is it works very well for cross-server tasks. One thing I’ve used it for quite often is comparing objects in databases between servers or instances. With straight TSQL this would require a linked server, and linked server queries can suffer a number of performance issues.
For this scenario, I have two instances, ServerA and ServerB. Both servers have the database MyDB which I suspect has different indexes. ServerA I know is running fine, so if there are any indexes there that are not in ServerB I want to script them out and create them. To complicate matters, there are tables in ServerA that don’t exist in ServerB so I need to be sure not to script out indexes on those tables or we will get errors.
I should mention that the SMO framework is automatically installed with SSMS, so make sure you try the code on a machine that has that installed.
To begin, we initialize the SMO framework and create some objects to represent the servers:
[Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | Out-Null
$ServerA = New-Object ("Microsoft.SqlServer.Management.Smo.Server") "ServerA.MyIntranet.Com"
$ServerB = New-Object ("Microsoft.SqlServer.Management.Smo.Server") "ServerB.MyIntranet.Com"
$DbName = 'MyDB'
The Out-Null is optional but just prevents output from the Assembly static method that loads SMO from cluttering up the console.
The code above assumes you will be using integrated security (Windows domain authentication). If you need to use SQL Server credentials, you can like so:
$ServerA.ConnectionContext.LoginSecure = $false
$ServerA.ConnectionContext.set_Login('sa')
$ServerA.ConnectionContext.set_Password('SomePassword')
Next we instantiate the database objects representing MyDB on each server. The server objects contain a collection of Databases which is keyed on the database name – indexing into it with the string of the name returns the object representing that specific database.
$ADB = $ServerA.Databases[$DBName]
$BDB = $ServerB.Databases[$DBName]
…then some empty hash tables to contain our index information, and an array to contain the tables the two DBs have in common.
Important Note: the remaining code assumes you don’t have the same table name in multiple schemas (i.e. both schemaone.Table and schematwo.Table). If you do, this gets a good bit more complicated but I can post that as well if someone needs it.
$AIndexes = @{}
$BIndexes = @{}
$CommonTables = @()
Now we populate the $CommonTables with only those tables that exist in both DBs:
($BDB.Tables).Name | Where-Object {($ADB.Tables).Name -contains $_} | ForEach-Object {$CommonTables += $_}
In short, we take the .Name property of all the tables in the $BDB.Tables collection, filter only for tables whose name exists in $ADB.Tables and then add those names to $CommonTables.
Almost done – now we build our list of indexes. We use a hash table because the KEY of the hash table will be the qualified name of the index – TableName.IndexName. The VALUE of the hash table will contain the actual SMO object representing the index which is much easier to deal with when we get to scripting.
$ADB.Tables | `
ForEach-Object {
$_.Indexes | Where-Object {($_.IsClustered -eq $false) -and ($CommonTables -contains $_.Parent.Name)} | ForEach-Object {$AIndexes.Add("$($_.Parent.Name).$($_.Name)", $_)}
}
$BDB.Tables | `
ForEach-Object {
$_.Indexes | Where-Object {($_.IsClustered -eq $false) -and ($CommonTables -contains $_.Parent.Name)} | ForEach-Object {$BIndexes.Add("$($_.Parent.Name).$($_.Name)", $_)}
}
This long pipe does a few things:
- Iterates through all the tables
- Filters out those not in our $CommonTables array
- Iterates through all the indexes on each table
- Filters out clustered indexes
- Adds the unfiltered indexes to the hash table, with the qualified name as the key and the object itself as the value
Finally, we compare the hash tables, build a string to dump our script into, and then script out all the missing indexes:
$NeededIndexes = $AIndexes.Keys | Where-Object {$BIndexes.Keys -notcontains $_}
[string]$IndexScripts = ''
$NeededIndexes | ForEach-Object { $IndexScripts += ($AIndexes.get_Item($_)).Script(); $IndexScripts += "`n`n"}
Now you have a string containing the script for all missing indexes on ServerB, and you can either output it to the console to run it manually, save it to a file, or even run it against the server if you like.
Full Script Below
[Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | Out-Null
$ServerA = New-Object ("Microsoft.SqlServer.Management.Smo.Server") "ServerA.MyIntranet.Com"
$ServerB = New-Object ("Microsoft.SqlServer.Management.Smo.Server") "ServerB.MyIntranet.Com"
$DbName = 'MyDB'
<# Optional - Add SQL Credentials to the connection
$ServerA.ConnectionContext.LoginSecure = $false
$ServerA.ConnectionContext.set_Login('sa')
$ServerA.ConnectionContext.set_Password('SomePassword')
#>
$ADB = $ServerA.Databases[$DBName]
$BDB = $ServerB.Databases[$DBName]
$AIndexes = @{}
$BIndexes = @{}
$CommonTables = @()
($BDB.Tables).Name | Where-Object {($ADB.Tables).Name -contains $_} | ForEach-Object {$CommonTables += $_}
$ADB.Tables | `
ForEach-Object {
$_.Indexes | Where-Object {($_.IsClustered -eq $false) -and ($CommonTables -contains $_.Parent.Name)} | ForEach-Object {$AIndexes.Add("$($_.Parent.Name).$($_.Name)", $_)}
}
$BDB.Tables | `
ForEach-Object {
$_.Indexes | Where-Object {($_.IsClustered -eq $false) -and ($CommonTables -contains $_.Parent.Name)} | ForEach-Object {$BIndexes.Add("$($_.Parent.Name).$($_.Name)", $_)}
}
$NeededIndexes = $AIndexes.Keys | Where-Object {$BIndexes.Keys -notcontains $_}
[string]$IndexScripts = ''
$NeededIndexes | ForEach-Object { $IndexScripts += ($AIndexes.get_Item($_)).Script(); $IndexScripts += "`n`n"}
Get-SQL 