Compare Database Table Counts

Say you've got two databases, supposed to be identical..... use this method to compare table counts across both databases.  You will create a 'countcompare' working table in both databases, collect the counts explicitly, and then join the two working tables, to compare the counts.  You will run steps 1 and 2 in both databases, and then run step 3 to join the working tables, and compare the counts.  (Your servers must be linked.)


/* 1. drop/recreate countcompare (working table) */
  use databasename;
  if object_id('countcompare','u')>0
  drop table countcompare;
  create table countcompare (
  tablename varchar(50),
  recordcount int
);


/* 2. collect table counts */
  use databasename;
  declare @tabcnt int
  declare @printline char (60)
  select @tabcnt = count (*) from sysobjects where type = 'U'


  If @tabcnt != 0
  BEGIN
   insert dbo.countcompare(tablename,recordcount)
   select 'TABLE NAME'= convert (varchar (50), o.name), 
   ROWS=i.rows
   from sysobjects o, sysindexes i
   where o.type = 'U'
   and o.id = i.id
   and i.indid in (0,1)
   order by o.name
  END


  select @printline = '(' + convert (varchar(10), @tabcnt) +'    
  tables in ' + DB_NAME() + ')'


  print ''
  print @printline


/* 3. local db joined to otherServer.otherDB, return table counts side by side. */
  select a.tablename,a.recordcount,b.recordcount 
  from localDB.dbo.countcompare a 
  join otherserver.otherDB.dbo.countcompare b
   on a.tablename = b.tablename
  and a.recordcount <> b.recordcount 


NOTE:  You can pull that last line out, if you want to see all table counts, instead of just the ones that vary.