sp_CompareTblCnts
This stored procedure does a quick table comparison between 2 databases that are supposed to be the same. Tables found on either db but missing from the other are reported as are record count differences for matching tables. Establish a linked-server with appropriate permissions to compare across servers. Compile in master.
if exists (select *
from dbo.sysobjects
where id = object_id('dbo.sp_CompareTblCnts')
and sysstat & 0xf = 4)
drop procedure dbo.sp_CompareTblCnts
Go
Create procedure dbo.sp_CompareTblCnts
@Srv1 sysname,
@Srv2 sysname,
@Db1 sysname,
@Db2 sysname,
@skiplisting int = 0
as
/***5***10***15***20***25***30***35***40***45***50***55***60***65***70***75**/--Name : sp_CompareTblCnts
--
--Description : Compares the record counts for all table between 2 databases
-- (assuming they are supposed to be the same). Tables missing
-- on either server from the other are reported. Total counts
-- for common tables are reported and count differences are
-- reported.
--
--Comments : In order to compare across 2 servers a linked server must be
-- set up with appropriate permissions to the remote server.
--
--Date : 06/21/2002
--Author : Clinton Herring
--
--History :
--
/***5***10***15***20***25***30***35***40***45***50***55***60***65***70***75**/
Set nocount on
-- If temp table exist drop them before attempting to create them.
If (Select object_id('tempdb.dbo.#SourceObjects')) > 0
Exec ('Drop table #SourceObjects')
-- Create temp tables to hold table list
Create table #SourceObjects(
server sysname null,
database_ sysname null,
name sysname null,
owner sysname null,
cnt1 int null,
cnt2 int null)
-- Declare variables
Declare @Owner sysname,
@Table sysname,
@objname sysname,
@Cmd varchar(512)
-- Set inital values
Select @Cmd = ''
Begin
-- Get table objects from each server
Select @Cmd = 'Insert into #SourceObjects(server, database_, name, owner) ' +
'Select ''' + @Srv1 + ''',''' + @Db1 + ''', a.name, b.name ' +
'From [' + @Srv1 + '].[' + @Db1 + '].[dbo].[sysobjects] a ' +
'Join [' + @Srv1 + '].[' + @Db1 + '].[dbo].[sysusers] b ' +
'On a.uid = b.uid ' +
'And a.type = ''U'' '
Exec (@Cmd)
Select @Cmd = 'Insert into #SourceObjects(server, database_, name, owner) ' +
'Select ''' + @Srv2 + ''',''' + @Db2 + ''', a.name, b.name ' +
'From [' + @Srv2 + '].[' + @Db2 + '].[dbo].[sysobjects] a ' +
'Join [' + @Srv2 + '].[' + @Db2 + '].[dbo].[sysusers] b ' +
'On a.uid = b.uid ' +
'And a.type = ''U'' '
Exec (@Cmd)
--Select * from #SourceObjects order by server, owner, name -- for test
Print REPLICATE ('*', 80)
Print 'Synchronization analysis of ' + @Srv1 + '.' + @Db1 + ' and ' +
@Srv2 + '.' + @Db2 + ' table counts.'
Print getdate()
Print ''
-- Check for extra objects on one server or the other
If (select count(*) from #SourceObjects where server = @Srv1 and database_ = @Db1 and owner + name not in
(select owner + name from #SourceObjects where server = @Srv2 and database_ = @Db2)) > 0
Begin
If @SkipListing = 0
Begin
Print 'These tables exist in ' + @Srv1 + '.' + @Db1 +
' but not in ' + @Srv2 + '.' + @Db2 + '...'
Select convert(varchar(60),' ' + owner + '.' + name)
from #SourceObjects
where server = @Srv1 and database_ = @Db1 and owner + name not in
(select owner + name
from #SourceObjects
where server = @Srv2 and database_ = @Db2) order by name, owner
End
Delete from #SourceObjects
where server = @Srv1 and database_ = @Db1 and owner + name not in
(select owner + name
from #SourceObjects where server = @Srv2 and database_ = @Db2)
Print ''
End
--Select * from #SourceObjects order by server, owner, name -- for test
If (select count(*) from #SourceObjects where server = @Srv2 and database_ = @Db2 and owner + name not in
(select owner + name from #SourceObjects where server = @Srv1 and database_ = @Db1)) > 0
Begin
If @SkipListing = 0
Begin
Print 'These tables exist in ' + @Srv2 + '.' + @Db2 +
' but not in ' + @Srv1 + '.' + @Db1 + '...'
Select convert(varchar(60),' ' + owner + '.' + name)
from #SourceObjects
where server = @Srv2 and database_ = @Db2 and owner + name not in
(select owner + name
from #SourceObjects
where server = @Srv1 and database_ = @Db1) order by name, owner
End
Print ''
End
Delete from #SourceObjects where server = @Srv2 and database_ = @Db2
--Select * from #SourceObjects order by server, owner, name -- for test
-- Get record counts for each common table and compare
Select @objname = min('[' + owner + '].[' + name + ']')
from #SourceObjects
While @objname is not null
Begin
Select @cmd = 'Update #SourceObjects set cnt1 = ' +
'(select count(*) from [' + @Srv1 + '].[' + @Db1 + '].' + @objname + ') ' +
'where ''['' + owner + ''].['' + name + '']'' = ''' + @objname + ''''
Exec (@cmd)
Select @cmd = 'Update #SourceObjects set cnt2 = ' +
'(select count(*) from ' + @Srv2 + '.' + @Db2 + '.' + @objname + ') ' +
'where ''['' + owner + ''].['' + name + '']'' = ''' + @objname + ''''
Exec (@cmd)
Select @objname = min('[' + owner + '].[' + name + ']')
from #SourceObjects
where '[' + owner + '].[' + name + ']'> @objname
End
If exists(Select * from #SourceObjects where abs(cnt1 - cnt2) > 0)
Begin
Print 'Differences'
Select @cmd = 'Select str(cnt1) + '' ('' + convert(varchar(34),owner + ''.'' + name) + '')'' ''' +
convert(varchar(50), @Srv1 + '.' + @Db1) + ''',' +
'str(cnt2) + '' ('' + convert(varchar(34),owner + ''.'' + name) + '')'' ''' +
convert(varchar(50), @Srv2 + '.' + @Db2) + ''',' +
'cnt1 - cnt2 ''Difference'' ' +
'from #SourceObjects where abs(cnt1 - cnt2) > 0 '
from #SourceObjects where abs(cnt1 - cnt2) > 0
Exec (@cmd)
Print ''
Print ''
End
Else
Begin
Print 'Record counts match for all common tables between ' +
@Srv1 + '.' + @Db1 + ' and ' + @Srv2 + '.' + @Db2
Print ''
Print ''
End
End
GO 
