SELECT T.NAME [TABLENAME], SUM(ST.ROW_COUNT)[ROWCOUNT]
FROM SYS.TABLES T
INNER JOIN SYS.SCHEMAS S
ON T.SCHEMA_ID = S.SCHEMA_ID
INNER JOIN SYS.INDEXES I
ON T.OBJECT_ID = I.OBJECT_ID
INNER JOIN SYS.DM_DB_PARTITION_STATS ST
ON T.OBJECT_ID = ST.OBJECT_ID
AND I.INDEX_ID = ST.INDEX_ID
WHERE I.INDEX_ID < 2
GROUP BY T.NAME
ORDER BY SUM(ST.ROW_COUNT) desc