UMG Developer (2/18/2011)

---

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;