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.