-- List partition functions, RangeType, Parameter type, Number of partitions
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;
-- List partition functions, RangeType, Parameter type, Number of partitions, it's value
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
, PRV.boundary_id
, PRV.value
, CASE WHEN PF.boundary_value_on_right = 0
THEN PRV.boundary_id ELSE PRV.boundary_id + 1 END AS PartitionNumber
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
JOIN sys.partition_range_values AS PRV
ON PP.function_id = PRV.function_id
AND PP.parameter_id = PRV.parameter_id;
--
SELECT
SPS.name AS PartitionSchemeName
, SDD.destination_id
, SF.name AS FileGroup
FROM sys.partition_schemes AS SPS
JOIN sys.destination_data_spaces AS SDD
ON SPS.data_space_id = SDD.partition_scheme_id
JOIN sys.filegroups AS SF
ON SF.data_space_id = SDD.data_space_id
WHERE SPS.name = 'psFIPS_GR01_PROP_DATA';
SELECT
SPS.name AS PartitionSchemeName
, SPF.name AS PartitionFunctionName
, CASE WHEN SDD.destination_id <= SPF.fanout THEN SDD.destination_id
ELSE NULL END AS PartitionID
, SF.name AS FileGroup
, CASE WHEN SDD.destination_id > SPF.fanout THEN 1
ELSE 0 END AS NextUsed
FROM sys.partition_schemes AS SPS
JOIN sys.partition_functions AS SPF
ON SPF.function_id = SPS.function_id
JOIN sys.destination_data_spaces AS SDD
ON SPS.data_space_id = SDD.partition_scheme_id
JOIN sys.filegroups AS SF
ON SF.data_space_id = SDD.data_space_id
WHERE SPS.name = 'psFIPS_GR01_PROP_DATA';
SELECT
SPS.name AS PartitionSchemeName
, CASE WHEN SDD.destination_id <= SPF.fanout THEN SDD.destination_id
ELSE NULL END AS PartitionID
, SPF.name AS PartitionFunctionName
, SPRV.value AS BoundaryValue
, CASE WHEN SDD.destination_id > SPF.fanout THEN 1
ELSE 0 END AS NextUsed
, SF.name AS FileGroup
FROM sys.partition_schemes AS SPS
JOIN sys.partition_functions AS SPF
ON SPS.function_id = SPF.function_id
JOIN sys.destination_data_spaces AS SDD
ON SDD.partition_scheme_id = SPS.data_space_id
JOIN sys.filegroups AS SF
ON SF.data_space_id = SDD.data_space_id
LEFT JOIN sys.partition_range_values AS SPRV
ON SPRV.function_id = SPF.function_id
AND SDD.destination_id =
CASE WHEN SPF.boundary_value_on_right = 0 THEN SPRV.boundary_id
ELSE SPRV.boundary_id + 1 END
WHERE SPS.name = 'psFIPS_GR01_PROP_DATA';
-- To just get a list of all partitioned tables (that is, tables that are created on a partition scheme), and their partition function, you can join the sys.partitions, sys.indexes, and sys.data_spaces catalog views.
SELECT
OBJECT_NAME(SI.OBJECT_ID) AS PartitionedTable
, DS.name AS PartitionScheme
FROM sys.indexes AS SI
JOIN sys.data_spaces AS DS
ON DS.data_space_id = SI.data_space_id
WHERE DS.type = 'PS'
AND OBJECTPROPERTYEX(SI.OBJECT_ID, 'BaseType') = 'U'
AND SI.index_id IN(0,1);
SELECT
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);
SELECT
OBJECT_NAME(SI.OBJECT_ID) AS PartitionedTable
, DS.name AS PartitionScheme
, PF.name AS PartitionFunction
, P.partition_number
, P.ROWS
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
WHERE DS.type = 'PS'
AND OBJECTPROPERTYEX(SI.OBJECT_ID, 'BaseType') = 'U'
AND SI.type IN(0,1);
SELECT
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);
SELECT
OBJECT_NAME(SI.OBJECT_ID) AS PartitionedTable
, SI.name
, SI.index_id
, DS.name AS PartitionScheme
FROM sys.indexes AS SI
JOIN sys.data_spaces AS DS
ON DS.data_space_id = SI.data_space_id
WHERE DS.type = 'PS';
SELECT
OBJECT_NAME(SI.OBJECT_ID) AS PartitionedTable
, SI.name
, SI.index_id
, DS.type
, DS.name AS PartitionScheme
FROM sys.indexes AS SI
JOIN sys.data_spaces AS DS
ON DS.data_space_id = SI.data_space_id
WHERE OBJECT_NAME(SI.OBJECT_ID) = 'TRANS'
SELECT
OBJECT_NAME(SI.OBJECT_ID) AS PartitionedTable
, DS.name AS PartitionScheme
, SI.name
, SI.index_id
, SP.partition_number
, SP.ROWS
FROM sys.indexes AS SI
JOIN sys.data_spaces AS DS
ON DS.data_space_id = SI.data_space_id
JOIN sys.partitions AS SP
ON SP.OBJECT_ID = SI.OBJECT_ID
AND SP.index_id = SI.index_id
WHERE DS.type = 'PS'
ORDER BY 1, 2, 3, 4, 5;