compare table counts in two databases

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

/*
 *   Created 10/2003 By Joe Toscano
 *   Any ideas/constructive criticism, ... send to jtoscano@ix.netcom.com
 *    
 *   This procedure gathers rowcounts of all tables in two databases that are passed as input 
 *   parameters.   To guarantee accuracy count(*) was used instead of relying on data in sysindexes.
 *   You may want to run this during non-critical times since it may take a while.
 *   Also,  if a table exists in one database, but not another this is noted by displaying 'Does Not Exist'
 *   in the Table Name column and N/A (not applicable) in the rowcount column.
 *   
 *   Assumptions:  We only look at tables owned by dbo.  Also, I am not using square brackets to allow for
 *                 table names that have embedded space (yuck!).   
 *
 *   Possible Improvements:  We are processing the tables on database at a time.  This means that
 *                           rowcounts may change in the second database while we are 'tallying up' rowcounts
 *                           in the first.  Perhaps this procedure could be modified to run on a table by table basis
 *                           which could mean for more accurate data ?
 *                           Also,  this procedure could be modified to do dirty reads (?)
 *
 */

if exists (select 1 from sysobjects where name = 'proc_compare_table_sizes' and type = 'P')
   drop proc proc_compare_table_sizes
go


CREATE proc proc_compare_table_sizes  
(@databaseone varchar(64) = 'icdTemplateDB',  
 @databasetwo varchar(64) = 'icloseTestDB')  
as  
  
  
set nocount on  
set CONCAT_NULL_YIELDS_NULL off  
  
DECLARE   
@id int,  
@Templatepages int,  
@NewDBpages int,  
@Templatereserved int,  
@NewDBreserved int,  
@objname varchar(750),  
@command varchar(2048),  
@tablename varchar(64),  
@rowcount int  
  

/*
**  See if the databases exists
*/if not exists (select * from master.dbo.sysdatabases
where (@databaseone is null or name = @databaseone))
begin
raiserror(15010,-1,-1,@databaseone)
  return (1)
end

if not exists (select * from master.dbo.sysdatabases
where (@databasetwo is null or name = @databasetwo))
begin
raiserror(15010,-1,-1,@databasetwo)
  return (1)
end


  
create table #allusertables  
( tablename varchar(100))  
  
  
CREATE TABLE #templatetables  
( tablename varchar (100),  
 rows int NULL         
)  
   
CREATE TABLE #newdbtables  
( tablename varchar (100),  
 rows int NULL  
)  
  
create table #tableinfo  
(tname varchar(64) NULL,  
 trows varchar(25) NULL,  
 nname varchar(64) NULL,  
 nrows varchar(25) NULL  
 )  
  
  
-- handle source database's user tables first.  @databaseone  
  
  
select @command = 'select name from  '  + @databaseone + '.dbo.sysobjects where xtype = ' + char(39) + 'U' + char(39) + ' and name not like ' + char(39) +  'dt%'  + char(39) +  ' and name not like ' +  char(39) + '%tempsrc%' + char(39) + ' order by name asc'  
insert #allusertables  
exec (@command)  
  
declare tablectr cursor for  
select tablename from #allusertables  
  
open tablectr  
  
fetch next from tablectr into @tablename  
  
while @@FETCH_STATUS = 0  
begin  
  
     select @command = 'Select count(*),'  +  char(39) + @tablename + char(39) + ' from ' + @databaseone + '.dbo.' +  @tablename + ' WITH (NOLOCK)'  
     insert #templatetables (rows, tablename)  
     exec (@command)   
          
     fetch tablectr into @tablename  
end  
  
close tablectr  
deallocate tablectr  
  
  
-- Handle the new database next.  @databasetwo  
  
  
truncate table #allusertables  
  
  
select @command = 'select name from ' +  @databasetwo + '.dbo.sysobjects where xtype = ' + char(39) + 'U' + char(39) + ' and name not like ' + char(39) + 'dt%' + char(39) + ' and name not like ' + char(39) + '%tempsrc%'+ char(39) + ' order by name asc'  
insert #allusertables  
exec (@command)  
  
   
  
declare tablectr2 cursor for  
select tablename from #allusertables  
  
open tablectr2  
  
fetch tablectr2 into @tablename  
  
while @@FETCH_STATUS = 0  
begin  
  
     select @command = 'Select count(*),'  +  char(39) + @tablename + char(39) + ' from ' + @databasetwo + '.dbo.' +  @tablename + ' WITH (NOLOCK)'  
  
     insert #newdbtables (rows, tablename)  
     exec (@command)   
          
     fetch tablectr2 into @tablename  
end  
  
close tablectr2  
deallocate tablectr2  
  
   
  
   
  
insert #tableinfo (tname, trows, nname, nrows)  
select t.tablename, convert(varchar(25), t.rows), n.tablename, convert(varchar(25), n.rows)
from #templatetables t  
inner join #newdbtables n  
on t.tablename = n.tablename  
  
   
insert #tableinfo (tname, trows, nname, nrows)  
select t.tablename, convert(varchar(25), t.rows), NULL, NULL  
from #templatetables t  
where t.tablename not in (select tablename from #newdbtables)  
  
  
insert #tableinfo (tname, trows, nname, nrows)  
select NULL, NULL, n.tablename, convert(varchar(25), n.rows  )
from #newdbtables n  
where n.tablename not in (select tablename from #templatetables)  
  
  
  
-- Only display the tables with different rowcounts
select @command = 'select isnull(tname, ' + char(39) + 'Does not Exist' + char(39) + ') as ' + char(39) + @databaseone + ' Tables' + char(39) + ', isnull(trows, ' + char(39) + 'N/A' + char(39) + ') as ' + char(39) +  @databaseone + ' Rows' + char(39) +  ', isnull(nname, ' + char(39) + 'Does Not Exist'  + char(39) + ') as ' + char(39) + @databasetwo + ' Tables' + char(39) + ', isnull(nrows, ' + char(39) + 'N/A' + char(39) + ') as ' + char(39) +  @databasetwo + ' Rows' + char(39) + ' from #tableinfo where isnull(nrows, 0) <> isnull(trows, 0)'  



-- display all table info
--select @command = 'select * from #tableinfo'


 --Only display the tables in which the New DB has a rowcount of 0 and the template db has a rowcount < 1000
 -- and the rowcounts differ

--select @command = 'select tname as ' + char(39) + @databaseone + ' Tables' + char(39) + ', trows as ' + char(39) +  @databaseone + ' Rows' + char(39) +  ', nname as ' + char(39) + @databasetwo + ' Tables' + char(39) + ', nrows as ' + char(39) +  @databasetwo + ' Rows' + char(39) + ' from #tableinfo where isnull(nrows, 0) <> isnull(trows, 0) and isnull(nrows, 0) = 0 and isnull(trows, 0) < 1000'  

 
select @rowcount = count(*) from #tableinfo
       where trows <> nrows
 

if @rowcount = 0
   RAISERROR   ('All tables exist in both databases (%s and %s)  and also have the same exact rowcount.', 16, 1, @databaseone, @databasetwo)
else
   exec (@command)  
  
  


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO