Partitioned Table – Number of Rows in Filegroups

Lately I met a problem with partitioned tables. I needed a report on number of rows of a partitioned table in different filegroups. It is quite easy to find number of rows in partitions with the following query:

SELECT $PARTITION.myRangePF1(col1),

       COUNT(*)

  FROM PartitionTable

GROUP BY $PARTITION.myRangePF1(col1)

However, finding number of rows in different filegroups was a bit harder. I managed to do it with the query bellow. I am not sure this is the best way, and that this query works in any situation. I hope it is going to be useful for somebody, and I would appreciate feedback.

WITH

 PartitionRowCount(Partition, NumberOfRowsInPartition) AS

  (

   SELECT $PARTITION.myRangePF1(col1),

          COUNT(*)

     FROM PartitionTable

   GROUP BY $PARTITION.myRangePF1(col1)

  ),

 PartitionsDataSpaces

  (partition_scheme_id, Partition, data_space_id, DataSpaceName) AS

  (

   SELECT dds.partition_scheme_id,

          dds.destination_id,

          dds.data_space_id,

          ds.name

     FROM sys.destination_data_spaces dds

          INNER JOIN sys.indexes i

           ON dds.partition_scheme_id = i.data_space_id

          INNER JOIN sys.data_spaces ds

           ON dds.data_space_id = ds.data_space_id

    WHERE i.object_id = OBJECT_ID(‘PartitionTable’)   

          AND i.index_id < 2

  )

SELECT pds.DataSpaceName,

       SUM(prc.NumberOfRowsInPartition) AS NumberOfRowsInDataSpace

  FROM PartitionRowCount prc

       INNER JOIN PartitionsDataSpaces pds

        ON prc.Partition = pds.Partition

GROUP BY pds.DataSpaceName;

GO