Skrypt zwracający szczegółową informację o partycjach i zakresach.

Przedstawiam poniżej skrypt, który zwraca szczegółową informację o partycjach i ich zakresach. W zmiennej @TableName należy podać nazwę badanej tabeli wraz ze schemą.

DECLARE @TableName SYSNAME = 'sales.InvoiceLinesPartitioning'
SELECT OBJECT_SCHEMA_NAME(pstats.object_id) AS SchemaName
	,OBJECT_NAME(pstats.object_id) AS TableName
	,ps.NAME AS PartitionSchemeName
	,ds.NAME AS PartitionFilegroupName
	,pf.NAME AS PartitionFunctionName
	,CASE pf.boundary_value_on_right
		WHEN 0
			THEN 'Range Left'
		ELSE 'Range Right'
		END AS PartitionFunctionRange
	,CASE pf.boundary_value_on_right
		WHEN 0
			THEN 'Upper Boundary'
		ELSE 'Lower Boundary'
		END AS PartitionBoundary
	,prv.value AS PartitionBoundaryValue
	,c.NAME AS PartitionKey
	,CASE 
		WHEN pf.boundary_value_on_right = 0
			THEN c.NAME + ' > ' + CAST(ISNULL(LAG(prv.value) OVER (
							PARTITION BY pstats.object_id ORDER BY pstats.object_id
							,pstats.partition_number), 'Infinity') AS VARCHAR(100)) 
							+ ' and ' + c.NAME + ' <= ' + CAST(ISNULL(prv.value, 'Infinity') AS VARCHAR(100)) 
                        ELSE c.NAME + ' >= ' + CAST(ISNULL(prv.value, 'Infinity') AS VARCHAR(100)) 
			  + ' and ' + c.NAME + ' < ' + CAST(ISNULL(LEAD(prv.value) OVER (
						PARTITION BY pstats.object_id ORDER BY pstats.object_id
						,pstats.partition_number), 'Infinity') AS VARCHAR(100))
		END AS PartitionRange
	,pstats.partition_number AS PartitionNumber
	,pstats.row_count AS PartitionRowCount
	,p.data_compression_desc AS DataCompression
FROM sys.dm_db_partition_stats AS pstats
INNER JOIN sys.partitions AS p ON pstats.partition_id = p.partition_id
INNER JOIN sys.destination_data_spaces AS dds ON pstats.partition_number = dds.destination_id
INNER JOIN sys.data_spaces AS ds ON dds.data_space_id = ds.data_space_id
INNER JOIN sys.partition_schemes AS ps ON dds.partition_scheme_id = ps.data_space_id
INNER JOIN sys.partition_functions AS pf ON ps.function_id = pf.function_id
INNER JOIN sys.indexes AS i ON pstats.object_id = i.object_id
	AND pstats.index_id = i.index_id
	AND dds.partition_scheme_id = i.data_space_id
	AND i.type <= 1 /* Heap or Clustered Index */ INNER JOIN sys.index_columns AS ic ON i.index_id = ic.index_id AND i.object_id = ic.object_id AND ic.partition_ordinal > 0
INNER JOIN sys.columns AS c ON pstats.object_id = c.object_id
	AND ic.column_id = c.column_id
LEFT JOIN sys.partition_range_values AS prv ON pf.function_id = prv.function_id
	AND pstats.partition_number = (
		CASE pf.boundary_value_on_right
			WHEN 0
				THEN prv.boundary_id
			ELSE (prv.boundary_id + 1)
			END
		)
WHERE pstats.object_id = OBJECT_ID(@TableName)
ORDER BY TableName, PartitionNumber;

Poniżej przedstawione są wyniki zapytania.