Counting all records in all tables

select
sysobjects.name
, sysindexes.rowcnt
from
sysindexes
inner join sysobjects
on sysindexes.id = sysobjects.id
where sysindexes.indid in (0, 1)
and objectproperty(sysobjects.id, 'IsuserTable') = 1 --Only user tables
order by 1


Heres an updated one that also returns the size used


SELECT a.Name
, SUM(b.RowCnt) EstRowCount
, SUM(b.Used) * 8 TableSize_KB
, (SUM(b.Used) * 8.0)/1024 TableSize_MB
FROM sysobjects a
LEFT OUTER JOIN sysindexes b
ON a.ID = b.ID
WHERE a.xtype IN ('U', 'S')
GROUP BY a.Name
ORDER BY SUM(b.RowCnt) desc ,a.Name