How to find the range values for a partition in Azure Synapse dedicated SQL pool

Question

I have a dbo.Test_Partitioned table in Azure Synapse dedicated SQL pool, which is range right partitioned on TEST_SEQ column.

I'm trying to automate the partition creation process for future data. If the last partition crosses 200 million records, we split it. For this I'm planning to use CTAS to create a new table to hold the data (to empty the partition) and, then split and then finally switch data back in.

I'm able to find the last partition number, but Is there a way to find the range value on which this last partition is created using query. I need to add a interval value to previous range split value to do a split.

Thanks in advance.

Answer 1

On further research, I got the result.

SELECT 
t.name AS TableName, 
i.name AS IndexName, 
p.partition_number,
p.partition_id,
f.type_desc,
c.name AS PartitioningColumnName,
CONVERT(nvarchar(255), r.value) as boundary
FROM sys.tables AS t
JOIN sys.indexes AS i
    ON t.object_id = i.object_id
JOIN sys.partitions AS p
    ON i.object_id = p.object_id AND i.index_id = p.index_id
JOIN  sys.partition_schemes AS s
    ON i.data_space_id = s.data_space_id
JOIN sys.partition_functions AS f
    ON s.function_id = f.function_id
LEFT JOIN sys.partition_range_values AS r
    ON f.function_id = r.function_id and r.boundary_id = p.partition_number
JOIN sys.index_columns AS ic
    ON ic.object_id = i.object_id
    AND ic.index_id = i.index_id AND ic.partition_ordinal > 0
JOIN sys.columns AS c
    ON t.object_id = c.object_id
WHERE t.name = @table
    AND c.column_id = ic.column_id
ORDER BY p.partition_number;