Counting objects between databases

I’ve been looking at using Powershell in our release process to automate various things. I’ve used it to compare table data between databases and I’m now thinking of using it to validate our schema upgrades. I want to be easily alerted to any missing tables, columns, stored procedures and other objects.

We have TFS at work so I could use this to perform schema checks but I find this rather cumbersome and it doesn’t lend itself to automation. I want to be able to run a script and have it alert me to any potential problems that need dealing with.

I’ve written a quick script to count the number of objects between two different databases. Just change the $server and $server2 variable to the SQL Server instances, and $database1 and $database2 to the databases to compare.

?View Code POWERSHELL
# Databases we want to compare
$server1 = "localhost\sqlexpress";
$database1 = "AdventureWorks";
$server2 = "localhost\sqlexpress";
$database2 = "AdventureWorks";
 
# Load SMO
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | Out-Null;
 
# Create sql server objects for source and destination servers
$srv1 = New-Object Microsoft.SqlServer.Management.SMO.Server $server1;
$srv2 = New-Object Microsoft.SqlServer.Management.SMO.Server $server2;
 
# IsSystemObject not returned by default so ask SMO for it
$srv1.SetDefaultInitFields([Microsoft.SqlServer.Management.SMO.Table], "IsSystemObject");
$srv1.SetDefaultInitFields([Microsoft.SqlServer.Management.SMO.View], "IsSystemObject");
$srv1.SetDefaultInitFields([Microsoft.SqlServer.Management.SMO.StoredProcedure], "IsSystemObject");
$srv1.SetDefaultInitFields([Microsoft.SqlServer.Management.SMO.Trigger], "IsSystemObject");
$srv1.SetDefaultInitFields([Microsoft.SqlServer.Management.SMO.UserDefinedFunction], "IsSystemObject");
$srv2.SetDefaultInitFields([Microsoft.SqlServer.Management.SMO.Table], "IsSystemObject");
$srv2.SetDefaultInitFields([Microsoft.SqlServer.Management.SMO.View], "IsSystemObject");
$srv2.SetDefaultInitFields([Microsoft.SqlServer.Management.SMO.StoredProcedure], "IsSystemObject");
$srv2.SetDefaultInitFields([Microsoft.SqlServer.Management.SMO.Trigger], "IsSystemObject");
$srv2.SetDefaultInitFields([Microsoft.SqlServer.Management.SMO.UserDefinedFunction], "IsSystemObject");
 
# get databases
$db1 = $srv1.Databases[$database1];
$db2 = $srv2.Databases[$database2];
 
# Get table count
$db1_tables = $($db1.Tables | Where-Object {$_.IsSystemObject -eq $false}).Count;
$db2_tables = $($db2.Tables | Where-Object {$_.IsSystemObject -eq $false}).Count;
 
# count indexes on tables
foreach($table in $db1.Tables)
{
                $db1_indexes += $table.Indexes.Count;
}
 
foreach($table in $db2.Tables)
{
                $db2_indexes += $table.Indexes.Count;
}
 
# count triggers on tables
foreach($table in $db1.Tables)
{
                $db1_triggers += $table.Triggers.Count;
}
 
foreach($table in $db2.Tables)
{
                $db2_triggers += $table.Triggers.Count;
}
 
# Count views
$db1_views = $($db1.Views | Where-Object {$_.IsSystemObject -eq $false}).Count;
$db2_views = $($db2.Views | Where-Object {$_.IsSystemObject -eq $false}).Count;
 
# Count procs
$db1_procs = $($db1.StoredProcedures | Where-Object {$_.IsSystemObject -eq $false}).Count;
$db2_procs = $($db2.StoredProcedures | Where-Object {$_.IsSystemObject -eq $false}).Count;
 
# Count udfs
$db1_udfs = $($db1.UserDefinedFunctions | Where-Object {$_.IsSystemObject -eq $false}).Count;
$db2_udfs = $($db2.UserDefinedFunctions | Where-Object {$_.IsSystemObject -eq $false}).Count;
 
# Set nulls to zero
if($db1_tables -eq $null)
{
	$db1_tables = 0;
}
if($db2_tables -eq $null)
{
	$db2_tables = 0;
}
if($db1_indexes -eq $null)
{
	$db1_indexes = 0;
}
if($db2_indexes -eq $null)
{
	$db2_indexes = 0;
}
if($db1_triggers -eq $null)
{
	$db1_triggers = 0;
}
if($db2_triggers -eq $null)
{
	$db2_triggers = 0;
}
if($db1_views -eq $null)
{
	$db1_views = 0;
}
if($db2_views -eq $null)
{
	$db2_views = 0;
}
if($db1_procs -eq $null)
{
	$db1_procs = 0;
}
if($db2_procs -eq $null)
{
	$db2_procs = 0;
}
if($db1_udfs -eq $null)
{
	$db1_udfs = 0;
}
if($db2_udfs -eq $null)
{
	$db2_udfs = 0;
}
 
# Output results
Write-Host "Object`t`t`tdb1`t`t`tdb2"
Write-Host "Tables`t`t`t$db1_tables`t`t`t$db2_tables";
Write-Host "Indexes`t`t`t$db1_indexes`t`t`t$db2_indexes";
Write-Host "Triggers`t`t`t$db1_triggers`t`t`t$db2_triggers";
Write-Host "Views`t`t`t$db1_views`t`t`t$db2_views";
Write-Host "Procs`t`t`t$db1_procs`t`t`t$db2_procs";
Write-Host "UDFs`t`t`t`t$db1_udfs`t`t`t$db2_udfs";

Here’s what the script outputs when executed against a copy of the Adventureworks database;

Object			db1			db2
Tables			70			70
Indexes			323			323
Triggers		11			11
Views			17			17
Procs			9			9
UDFs			11			11

This script is really just POC. Just because we have the same number of objects does not necessarily mean we have the correct ones across environments. To be of more practical value I need to be checking object names and definitions. Watch this space!


2 Comments

  1. Rhys, you addressed the first point I was going to make, which is that a count in and of itself is not all that indicative about what is different. You could have a new table in one environment and delete a table in the other, and the counts look identical.

    The other point I’d like to raise is that once you spend more than a couple of hours developing a solution for this kind of task, you’re repeating the work of countless folks before you, and you still haven’t come across many of the complications that they’ve already dealt with. I wrote a post about this a couple of years ago that uses this very problem as an example:

    http://www.onetooneglobal.com/otocorporate-posts/2009/06/01/the-cost-of-reinventing-the-wheel/

  2. Rhys says:

    Hi Aaron,

    I’ve actually already implemented some ideas based on this. We have an archive database that contains tables of the same name to the primary database. Dev’s occassionally forget to add columns to the archive db. I’ve got a script that checks the appropriate archive table, compares the column names, data types and lengths and alerts me to any differences. Simple and useful for me.

    I’m in no way on about creating a database sync tool (that would be madness!). I’ve done this with Powershell because I can automate this check and email it out. There’s no way to do that with TFS as far as I’m aware. The aim of this isn’t to syncronise databases but to automate a few very simple checks. that I otherwise might not get around to doing. I agree anything complex then go for one of the commercial tools (they’ll pay for themselves in a matter of weeks).

    But for simple things, like checking proc names and definitions across environments, Powershell is ideal. With a script like this I can see if anyone has sneaked a proc into somewhere they shouldn’t have.

    Cheers,

    Rhys

Leave a Reply