My project is on the move, literally. We're migrating from our old data center to a new one; everything is being brought up on new hardware. Since we're using full and incremental backups, an important sanity check is to make sure all the data has been restored, which is being done using the following query:
select
'select ''' + TABLE_SCHEMA + ''' as TableSchema, ''' + TABLE_NAME + ''' as TableName, count(*) as [Rows] from ' + TABLE_SCHEMA + '.' + TABLE_NAME + ' union' as [SQL]
from
INFORMATION_SCHEMA.TABLES
where
TABLE_TYPE = 'BASE TABLE'
The query above produces a query. When run against the AdventureWorks database you get:
select 'Production' as TableSchema, 'ProductProductPhoto' as TableName, count(*) as [Rows] from Production.ProductProductPhoto union
-- many rows removed
select 'Production' as TableSchema, 'ProductPhoto' as TableName, count(*) as [Rows] from Production.ProductPhoto union
The result isn't perfect. The last union operator needs to be replaced by an order by:
select 'Production' as TableSchema, 'ProductPhoto' as TableName, count(*) as [Rows] from Production.ProductPhoto
order by TableSchema, TableName
When you run the fixed up query you'll get a list of every table in a database along with the number of rows in each table:
If there's too many tables to compare by hand, save the results from the source and destination databases to text files and compare using WinMerge.