Skip to main content
Malcan Logo

Polski  English  
  
Malcan > Tips and tricks > Rows counts - getting quickly, comparing databases  

Tips and tricks: Rows counts - getting quickly, comparing databases

See also Malcan Workflow for Sharepoint

Technology

SQL Server 

Title

Rows counts - getting quickly, comparing databases 

Hint

Just to get all tables with their row counts quickly:
select so.name, si.rows
from sysindexes si inner join sysobjects so on si.id=so.id
where si.indid<2 and so.xtype='U'
order by si.rows desc
 
Also a combination if you want to compare rows counts between two databases e.g. to check if they are the same (in terms of row counts).
select so1.name, si1.rows, si2.rows, si1.rows-si2.rows from DB1..sysindexes si1
 inner join DB1..sysobjects so1 on si1.id=so1.id
 inner join DB2..sysobjects so2 on so1.name=so2.name
 inner join DB2..sysindexes si2 on si2.id=so2.id
 where si1.indid<2 and so1.xtype='U'and si2.indid<2 and so2.xtype='U' and si1.rows-si2.rows<>0
 order by so1.name
 
Note:
The results may be not accurate due to the values in SQL system tables (it may require exhoustive DBCC UPDATEUSAGE). Usually they are correct and if you really want to compare I would suggest to use this script first and then investigate just few differences.
It works only on tables with an index on them.
Attachments
Created at 12/11/2008 14:26  by Artur Malinowski 
Last modified at 12/22/2008 9:22  by Artur Malinowski