Comparing Database Counts

Recently my company did an upgrade of a vendor-supplied product from one version to another. The new version includes some schema changes to the database. Just to be safe, we decided it prudent to create a new database on our SQL Server 2000 system and use the vendor’s migration tool to transform the data. As there was some customization done to the original source database, we wanted to be sure we had all the rows copied. It took a bit of research to find this but here’s an example of how to write a quick and dirty query in MS SQL Server to compare table-by-table two databases.

I figured that a temp table would be useful here, so I could do a simple join on the two.

The code is listed below. It is 

use MySourceDatabase
select o.name, rows  into #temp1 
from sysobjects o inner join sysindexes i on o.id = i.id
where i.indid < 2
order by o.name

use MyDestinationDatabase
select o.name, rows into #temp2
from sysobjects o inner join sysindexes i on o.id = i.id
where i.indid < 2

order by o.name

select 
	#temp1.name, 
	#temp1.rows as SourceDBCount,
	#temp2.rows as DestDBCount
from
	#temp1, #temp2
where
	#temp1.name = #temp2.name

What we have here is the following

use MySourceDatabase
select o.name, rows into #temp1 
from sysobjects o inner join sysindexes i on o.id = i.id
where i.indid < 2
order by o.name

This statement sets up the first querey. It instructs SQL Server to use the source database and to put the name and number of rows for each “object” into a table called #temp1. (Temp tables are – in my opinion – invaluable for data manipulation.) The temp table is automatically generated by the query and will be destroyed as soon as I close the Query Analyzer window. 

use MyDestinationDatabase
select o.name, rows into #temp2
from sysobjects o inner join sysindexes i on o.id = i.id
where i.indid < 2
order by o.name

This is more-or-less a copy of the first statement. It allows the data from the second – or desitination – table to be copied into a second temp table. 

I now have two temp tables with the same columns. We can then do a comparison. Since I know the structure of the two databases are the same I can perform a query on the two tables to determine what the rows are in each.

select 
	#temp1.name, 
	#temp1.rows as SourceDBCount,
	#temp2.rows as DestDBCount
from
	#temp1, #temp2
where
	#temp1.name = #temp2.name

Here I join the two temp tables and select the name and rows from each. (I only need to use name from one since it’ll be the same on both.)

The  as function in SQL allows me to label the two count rows so I can ensure I know which table is being listed. If I wanted to go further, I could enhance the query to show only those rows where the number is not the same….

select 
	#temp1.name, 
	#temp1.rows as SourceDBCount,
	#temp2.rows as DestDBCount
from
	#temp1, #temp2
where
	#temp1.name = #temp2.name
and
	#temp1.rows <> #temp2.rows

…or whre the number is the same:

select 
	#temp1.name, 
	#temp1.rows as SourceDBCount,
	#temp2.rows as DestDBCount
from
	#temp1, #temp2
where
	#temp1.name = #temp2.name
and
	#temp1.rows = #temp2.rows

I hope this information is useful in helping you get the right data transferred.