SELECT
SCHEMA_NAME(tbl.schema_id) + '.' + OBJECT_NAME(tbl.object_id) TableName,
idx.name IndexName,
ps.name PartitionScheme,
idxpf.PartitionFunction PartitionFunction,
CASE WHEN idxpf.boundary_value_on_right = 1 THEN 'RIGHT' 
    WHEN idxpf.boundary_value_on_right = 0 THEN 'LEFT' END RangeType,
p.partition_number AS PartitionNumber,
prv.value AS BoundaryValue,
CAST(p.rows AS float) AS NumRows,
 fg.name AS FileGroupName,
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
INNER 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
INNER JOIN (
    SELECT DISTINCT indx.name IndexName, pf.name PartitionFunction, pf.boundary_value_on_right
    FROM sys.indexes AS indx INNER JOIN sys.partition_schemes AS ps ON ps.data_space_id = indx.data_space_id
    INNER JOIN sys.partitions AS p ON p.object_id = indx.object_id AND p.index_id=indx.index_id
    INNER JOIN sys.partition_range_values AS prv ON prv.boundary_id = p.partition_number and prv.function_id = ps.function_id
    INNER JOIN sys.partition_functions AS pf ON  pf.function_id = prv.function_id) idxpf ON idxpf.IndexName = idx.name

