Get partition ranges or partitions of given range

fn_GetPartitionsForRange.sql

-- =============================================
-- Author:		Josiah Siegel
-- Create date: 2024-10-21
-- Description:	Fetch partition ranges or partitions of given range
-- Example:
/*
-- Fetch partition ranges
select * from [fn_GetPartitionsForRange]('MyTable', DEFAULT, DEFAULT, DEFAULT)

-- Fetch partitions of given range
select * from [fn_GetPartitionsForRange]('MyTable', DEFAULT, 20160101, 20160201)
select * from [fn_GetPartitionsForRange]('MyTable2', 'MySchema', 20160101000000000, 20160201000000000)

-- Filter query
SELECT COUNT(*)
FROM [dbo].[MyTable] AS d
JOIN [fn_GetPartitionsForRange]('MyTable', DEFAULT, 20220101, 20220131) AS p
ON $PARTITION.[MyPartitionFunction](DateColumn) = p.partition_number
WHERE d.DateColumn BETWEEN 20220101 AND 20220131;
*/
-- =============================================
CREATE OR ALTER FUNCTION [fn_GetPartitionsForRange] 
(
    @TableName NVARCHAR(128),
    @SchemaName NVARCHAR(128) = 'dbo',
    @StartValue SQL_VARIANT = NULL,
    @EndValue SQL_VARIANT = NULL
)
RETURNS TABLE
AS
RETURN
(
    WITH PartitionBoundaries AS (
        SELECT 
            p.partition_number,
            p.rows,
            prv.value AS boundary_value,
            prv.boundary_id,
            LAG(prv.value) OVER (ORDER BY p.partition_number) AS prev_boundary_value,
            LEAD(prv.value) OVER (ORDER BY p.partition_number) AS next_boundary_value,
            pf.boundary_value_on_right,
            '$PARTITION.' + QUOTENAME(pf.name) + '(' + QUOTENAME(c.name) + ')' AS [example_filter]
        FROM sys.tables t WITH (NOLOCK)
        JOIN sys.indexes i WITH (NOLOCK) ON t.object_id = i.object_id
        JOIN sys.index_columns AS ic WITH (NOLOCK) ON ic.[object_id] = i.[object_id] AND ic.index_id = i.index_id AND ic.partition_ordinal >= 1
        JOIN sys.columns AS c WITH (NOLOCK) ON t.[object_id] = c.[object_id] AND ic.column_id = c.column_id   
        JOIN sys.partitions p WITH (NOLOCK) ON i.object_id = p.object_id AND i.index_id = p.index_id
        JOIN sys.partition_schemes ps WITH (NOLOCK) ON i.data_space_id = ps.data_space_id
        JOIN sys.partition_functions pf WITH (NOLOCK) ON ps.function_id = pf.function_id
        LEFT JOIN sys.partition_range_values prv WITH (NOLOCK) ON pf.function_id = prv.function_id AND p.partition_number = prv.boundary_id
        WHERE t.name = @TableName
        AND SCHEMA_NAME(t.schema_id) = @SchemaName
        AND i.index_id < 2 -- Assuming the partitioning is on the clustered index or heap
    ),
    PartitionRanges AS (
        SELECT 
            partition_number,
            rows,
            boundary_value_on_right,
            boundary_value,
            prev_boundary_value,
            next_boundary_value,
            CASE 
                WHEN boundary_value_on_right = 1 THEN '>='
                ELSE '>'
            END AS min_operator,
            CASE 
                WHEN boundary_value_on_right = 1 THEN ISNULL(prev_boundary_value, 0)
                ELSE boundary_value
            END AS min_value,
            CASE 
                WHEN boundary_value_on_right = 1 THEN '<'
                ELSE '<='
            END AS max_operator,
            CASE 
                WHEN boundary_value_on_right = 1 THEN boundary_value
                ELSE next_boundary_value
            END AS max_value,
            [example_filter]
        FROM PartitionBoundaries
    )
    SELECT partition_number, rows, min_operator, min_value, max_operator, max_value, [example_filter]
    FROM PartitionRanges
    WHERE 
        (   (CAST(@StartValue AS SQL_VARIANT) IS NULL AND CAST(@EndValue AS SQL_VARIANT) IS NULL)
            OR
            (boundary_value_on_right = 1 AND CAST(@StartValue AS SQL_VARIANT) >= min_value AND CAST(@EndValue AS SQL_VARIANT) < next_boundary_value)
            OR
            (boundary_value_on_right = 0 AND CAST(@StartValue AS SQL_VARIANT) > prev_boundary_value AND CAST(@EndValue AS SQL_VARIANT) <= max_value)
        )
);