There are many ways to go about determining the count of rows in each table in a database. Including cursors and brute force row counts - but from what I understand there is only one way to get the row count efficiently - and that's to use the table's indexes.

SELECT
	SysO.name AS [Table],
	MAX(SysI.[rows]) AS [Rows]
FROM
	sys.objects As SysO
INNER JOIN sysindexes AS SysI
	ON SysI.id = SysO.object_id
WHERE
	SysO.type = 'u'
GROUP BY
	SysO.name