Sqlserver 查看表分区及分区行数

最新推荐文章于 2025-07-28 14:15:20 发布

原创最新推荐文章于 2025-07-28 14:15:20 发布 · 4.7k 阅读

1 ·

CC 4.0 BY-SA版权

数据库同时被 2 个专栏收录

5 篇文章

订阅专栏

----sqlserver

1 篇文章

订阅专栏

本文介绍了一种使用SQL查询分区表的方法，通过连接多个系统表来获取详细的分区信息，包括分区方案、分区号、文件组、范围边界及每一分区中的行数等。此查询适用于深入理解SQL Server中特定表（例如示例中的'wfpuser_t0305logs'）的分区细节。

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('wfpuser_t0305logs')--分区表名
and i.index_id in (0, 1)

order by p.partition_number