Where has sysindex.rows gone?

Question

In SqlServer 2000, there used to be a table called sysindex which had a column rows. This table is now: -sys.indexes -sys.partitions -sys.allocation_units -sys.dm_db_partition_stats

(see: http://msdn.microsoft.com/en-us/library/ms187997.aspx) but none of these tables contain the row columns? So how do you find out how many rows there are for an index?

Thanks.

Answer 1

It is still there in the compatibility view sys.sysindexes.

You can also use

SELECT OBJECT_NAME(p.object_id), SUM(rows)
FROM sys.partitions p 
WHERE index_id < 2 and OBJECTPROPERTYEX (object_id ,'IsUserTable' ) = 1
GROUP BY p.object_id

This should not be taken as in any way guaranteed accurate. It includes the effects of uncommitted transactions, forwarding pointers on a heap and can be manually updated with some undocumented parameters to UPDATE STATISTICS.