SELECT s.name AS TABLE_SCHEMA, 
       o.name AS TABLE_NAME, 
       i.name AS INDEX_NAME, 
       f.name AS PARTITION_FUNCTION, 
       ps.name AS PARTITION_SCHEMA, 
       p.partition_number AS PART_NUM, 
       fg.name AS FILE_GROUP, 
       rows AS ROW_COUNT, 
       SUM(dbf.size) OVER(PARTITION BY fg.name) AS PAGE_COUNT, 
       au.total_pages AS USED_PAGES,
       CASE boundary_value_on_right
           WHEN 1
           THEN 'RIGHT'
           ELSE 'LEFT'
       END AS RANGE, 
       rv1.value AS LOW_VALUE, 
       rv2.value AS HIGH_VALUE
FROM sys.partitions p
     JOIN sys.indexes i
     ON p.object_id = i.object_id
        AND p.index_id = i.index_id
     JOIN sys.objects AS o
     ON i.object_id = o.object_id
     JOIN sys.schemas AS s
     ON o.schema_id = s.schema_id
     JOIN sys.partition_schemes ps
     ON ps.data_space_id = i.data_space_id
     JOIN sys.partition_functions f
     ON f.function_id = ps.function_id
     JOIN sys.destination_data_spaces dds
     ON dds.partition_scheme_id = ps.data_space_id
        AND dds.destination_id = p.partition_number
     JOIN sys.filegroups fg
     ON dds.data_space_id = fg.data_space_id
     JOIN sys.database_files AS dbf
     ON dbf.data_space_id = fg.data_space_id
     JOIN sys.allocation_units au
     ON au.container_id = p.partition_id
     LEFT OUTER JOIN sys.partition_range_values rv2
     ON f.function_id = rv2.function_id
        AND p.partition_number = rv2.boundary_id
     LEFT OUTER JOIN sys.partition_range_values rv1
     ON f.function_id = rv1.function_id
        AND p.partition_number - 1 = rv1.boundary_id
ORDER BY TABLE_SCHEMA, 
         TABLE_NAME, 
         INDEX_NAME, 
         LOW_VALUE;