PowerShell Quickie: Compare tables and row counts in two databases on different servers

Sometimes, PowerShell scripts for SQL Server don’t need to be super fancy to be useful. In fact, you may just need to write a quick script to get something done. I had a particular challenge the other day I needed to solve quickly, so I turned to PowerShell. The problem: I was working with some business partners to archive off some old data and tables to an archive server. Before I could pull the trigger and drop the tables, they needed proof that not only was everything was there, but all the rows were too. Since these databases are on different servers, without a linked server (and all that entails), I decided to use PowerShell to quickly compare tables on both databases on the different servers. I ended up with this:

$sourceTables = Get-ChildItem -Path 'SQLSERVER:\SQL\SourceServer\default\Databases\ReportingSubscriber\Tables'
$targetTables = Get-ChildItem -Path 'SQLSERVER:\SQL\Destination\default\Databases\ReportingSubscriber\Tables'

$counter = 0

$tables = @()

ForEach ($t in $sourceTables)
{
    $counter++
    $tableInfo = New-Object System.Object
    $tableInfo | Add-Member -type NoteProperty -name TableNumber -Value $counter
    $tableInfo | Add-Member -type NoteProperty -name SourceTableSchemaName -Value $t.Schema
    $tableInfo | Add-Member -type NoteProperty -name SourceTableName -Value $t.Name
    $tableInfo | Add-Member -type NoteProperty -name SourceRows -Value $t.RowCount
    $tableInfo | Add-Member -type NoteProperty -name DestinationSchemaName -Value ($targetTables | Where-Object {$_.Schema -eq $t.Schema -and $_.Name -eq $t.Name}).Schema
    $tableInfo | Add-Member -type NoteProperty -name DestinationTableName -Value ($targetTables | Where-Object {$_.Schema -eq $t.Schema -and $_.Name -eq $t.Name}).Name
    $tableInfo | Add-Member -type NoteProperty -name DestinationRows -Value ($targetTables | Where-Object {$_.Schema -eq $t.Schema -and $_.Name -eq $t.Name}).RowCount
    $tables += $tableInfo
}
    
$tables | Export-CSV -Path tableCompare.csv -NoTypeInformation

It’s nothing fancy: using the SQLPS module I am grabbing objects from both servers that contain all the tables inside a given database. Then, I declare an array called $tables that will hold all my results. For-Each is great because it lets me enumerate over the entire collection of tables, where I also am creating a custom object with New-Object System.Object. Each table’s schema name and table name get added to the object, plus the row count for the table. I then take the object that is holding all the table information from the destination and filter it based on the name from the source, and get the same properties. When I’m done, I take the array of objects and export it to a CSV file.

Remember: you don’t need to write a foolproof cmdlet or function every time you have a problem. Sometimes, you just need some code. It is just yet another reason PowerShell is so awesome.