Nice little script, but it will return multiple rows for partitioned tables. Here is an updated version that takes care of that:
SELECT
o.name AS "Table Name",
SUM(i.rowcnt) AS "Row Count"
FROM sysobjects o
INNER JOIN sysindexes i
ON o.id = i.id
WHERE
i.indid IN (0, 1)
AND o.xtype = 'u'
AND o.name <> 'sysdiagrams'
GROUP BY
o.name
ORDER BY
"Row Count" DESC;
I, also, converted it to use an INNER JOIN instead of doing the join in the WHERE, as I think it makes the codes more readable.
Of course I think that in 2005/2008/2008R2 the preferred method is to use sys.dm_db_partition_stats:
SELECT
OBJECT_NAME(object_id) AS "Table Name",
SUM(row_count) AS "Row Count"
FROM sys.dm_db_partition_stats
WHERE
index_id < 2
AND OBJECTPROPERTY(object_id, 'IsMSShipped') = 0
GROUP BY
object_id
ORDER BY
"Row Count" DESC;