This script counts the number of rows in all objects in the current database.

select TableName = so.name, [RowCount] = MAX(si.rows)
from sysobjects as so, sysindexes as si
where so.xtype = 'U' and si.id = OBJECT_ID(so.name)
group by so.name
order by 1;