Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
add comment

1 Answer

up vote 5 down vote accepted

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.