如何在Azure Synapse专用SQL池中查找分区的范围值

Question

我在Azure专用的dbo.Test_Partitioned池中有一个TEST_SEQ表，它是在TEST_SEQ列上右分区的范围。

我正在尝试为将来的数据自动化分区创建过程。如果最后一个分区超过2亿条记录，我们将其拆分。为此，我计划使用CTAS创建一个新的表来保存数据(清空分区)，然后拆分，然后再切换回数据。

我能够找到最后一个分区号，但是是否有一种方法可以找到使用query创建最后一个分区的范围值。我需要向以前的范围拆分值添加一个间隔值来执行拆分。

提前谢谢。

Answer 1

在进一步的研究中，我得到了结果。

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;