get row count for a table SQL Server DMVs

In SQL 2000 you can get this informartion from sysindexes system table.
But we should not sysindexes in because it will be deprecated in future

After 2005 this information is stored with partition details   because table is logically atleast a single partition
This provides estimated row  count information based on last updated stats.

lets look with more details
SELECT Object_name(object_id),
       CASE index_id
         WHEN 0 THEN 'Heap'
         WHEN 1 THEN 'Clustered'
       END,
       partition_id,
       object_id,
       index_id,
       rows,
       data_compression_desc
FROM   sys.partitions
WHERE  object_id > 100
       AND index_id IN ( 0, 1 )
ORDER  BY Object_name(object_id)




Same details we can get from sys.dm_db_partition_stats


SELECT Object_name (object_id),
       partition_id,
       CASE index_id
         WHEN 0 THEN 'Heap'
         WHEN 1 THEN 'Clustered'
       END,
       row_count
FROM   sys.dm_db_partition_stats
WHERE  object_id > 100
       AND index_id IN ( 0, 1 )
ORDER  BY Object_name(object_id)


No comments:

Post a Comment

Amazon Contextual Product Ads

Popular Posts