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)