SELECT
i.object_id,
i.name AS IndexName,
p.partition_number,
fg.name AS FileGroupName,
value,
ps.name AS SchemaName,
f.name FunctionName
FROM sys.partitions p
JOIN sys.indexes i
ON (p.object_id = i.object_id
AND p.index_id = i.index_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)
LEFT JOIN sys.partition_range_values rv
ON (f.function_id = rv.function_id
AND p.partition_number = rv.boundary_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)
WHERE i.index_id < 2
AND i.object_id = Object_Id('<ObjectName>')
Partition of SQL Server Information Retrieval:
SELECT * FROM SYS.dm_db_partition_statsSELECT * FROM SYS.partition_functionsSELECT * FROM SYS.partition_parametersSELECT * FROM SYS.partition_schemesSELECT * FROM SYS.partitionsSELECT * FROM SYS.system_internals_partition_columnsSELECT * FROM SYS.system_internals_partitions
