-- Partition Functions:
SELECT PF.function_id, PF.name, PF.fanout AS NumPartitions,
CASE WHEN PF.boundary_value_on_right = 0 THEN 'LEFT' ELSE 'RIGHT' END AS RangeType,
PP.parameter_id,
CASE WHEN PP.system_type_id = PP.user_type_id THEN T1.name ELSE T2.name END AS ParameterDataType
FROM sys.partition_functions AS PF
JOIN sys.partition_parameters AS PP ON PF.function_id = PP.function_id
JOIN sys.types AS T1 ON T1.system_type_id = PP.system_type_id
JOIN sys.types AS T2 ON T2.user_type_id= PP.user_type_id
-- Index - Partitioned Table - Partition Scheme - Partition - Function
SELECT SI.name AS IndexName, OBJECT_NAME(SI.object_id) AS PartitionedTable,
DS.name AS PartitionScheme, PF.name AS PartitionFunction
FROM sys.indexes AS SI
JOIN sys.data_spaces AS DS ON DS.data_space_id = SI.data_space_id
JOIN sys.partition_schemes AS PS ON PS.data_space_id = SI.data_space_id
JOIN sys.partition_functions AS PF ON PF.function_id = PS.function_id
WHERE DS.type = 'PS' AND OBJECTPROPERTYEX(SI.object_id, 'BaseType') = 'U' AND SI.index_id IN(0,1)
-- Index - Partitioned Table - Partition Scheme - Partition - Function - more detailed
SELECT SI.name AS IndexName, OBJECT_NAME(SI.object_id) AS PartitionedTable, DS.name AS PartitionSchemeName, PF.name AS PartitionFunction,
P.partition_number AS PartitionNumber, P.rows AS PartitionRows, FG.name AS FileGroupName
FROM sys.partitions AS P
JOIN sys.indexes AS SI ON P.object_id = SI.object_id AND P.index_id = SI.index_id
JOIN sys.data_spaces AS DS ON DS.data_space_id = SI.data_space_id
JOIN sys.partition_schemes AS PS ON PS.data_space_id = SI.data_space_id
JOIN sys.partition_functions AS PF ON PF.function_id = PS.function_id
JOIN sys.destination_data_spaces AS DDS ON DDS.partition_scheme_id = SI.data_space_id AND DDS.destination_id = P.partition_number
JOIN sys.filegroups AS FG ON DDS.data_space_id = FG.data_space_id
WHERE DS.type = 'PS' AND OBJECTPROPERTYEX(SI.object_id, 'BaseType') = 'U' AND SI.type IN(0,1)
