SqlServer 表分区信息

安装

USE Test2; --Do
GO
--查询分区函数

SELECT *
FROM sys.partition_functions;
--查看分区架构

SELECT *
FROM sys.partition_schemes;


--查看分区信息

DECLARE @tableName VARCHAR(100);
SET @tableName = 'Sales'; --Do

SELECT CONVERT(VARCHAR(50), ps.name) AS partition_scheme,
       p.partition_number,
       CONVERT(VARCHAR(10), ds2.name) AS filegroup,
       CONVERT(VARCHAR(19), ISNULL(v.value, ''), 120) AS range_boundary,
       STR(p.rows, 9) AS rows
FROM sys.indexes i
     JOIN sys.partition_schemes ps ON i.data_space_id = ps.data_space_id
     JOIN sys.destination_data_spaces dds ON ps.data_space_id = dds.partition_scheme_id
     JOIN sys.data_spaces ds2 ON dds.data_space_id = ds2.data_space_id
     JOIN sys.partitions p ON dds.destination_id = p.partition_number
                              AND p.object_id = i.object_id
                              AND p.index_id = i.index_id
     JOIN sys.partition_functions pf ON ps.function_id = pf.function_id
     LEFT JOIN sys.partition_range_values v ON pf.function_id = v.function_id
                                               AND v.boundary_id = p.partition_number - pf.boundary_value_on_right
WHERE i.object_id = OBJECT_ID(@tableName)
      AND i.index_id IN(0, 1)
ORDER BY p.partition_number;

本文由 创作，采用 知识共享署名4.0 国际许可协议进行许可。本站文章除注明转载/出处外，均为本站原创或翻译，转载前请务必署名。最后编辑时间为: 2020/05/07 03:28