SELECT
   tbl.name                                AS TableName,
   p.partition_number                      AS PartitionNumber,
   prv.value                               AS RightBoundaryValue,
   CAST(p.rows AS FLOAT)                 AS [RowCount],
   fg.name                                 AS FileGroupName,
   CAST(pf.boundary_value_on_right AS INT) AS RangeType,  
   p.data_compression                      AS DataCompression
FROM
sys.tables AS tbl
INNER JOIN sys.indexes AS idx
   ON idx.object_id = tbl.object_id
INNER JOIN sys.partitions AS p
   ON p.object_id=CAST(tbl.object_id AS INT)
   AND p.index_id=idx.index_id
INNER JOIN sys.indexes AS indx
   ON p.object_id = indx.object_id
   and p.index_id = indx.index_id
LEFT OUTER JOIN sys.destination_data_spaces AS dds
   ON dds.partition_scheme_id = indx.data_space_id
   and dds.destination_id = p.partition_number
LEFT OUTER JOIN sys.partition_schemes AS ps
   ON ps.data_space_id = indx.data_space_id
LEFT OUTER JOIN sys.partition_range_values AS prv
   ON prv.boundary_id = p.partition_number
   and prv.function_id = ps.function_id
LEFT OUTER JOIN sys.filegroups AS fg
   ON fg.data_space_id = dds.data_space_id
   or fg.data_space_id = indx.data_space_id
LEFT OUTER JOIN sys.partition_functions AS pf
   ON pf.function_id = prv.function_id