Get Table Names and Row Counts

SELECT
    [TableName] = SO.[Name],
    [RowCount] = MAX(SI.ROWS)
FROM
    SysObjects SO,
    SysIndexes SI
WHERE
        SO.xtype = 'U'
AND SI.Id = OBJECT_ID(SO.[Name])
GROUP BY
    SO.[Name]
ORDER BY
    2 DESC