SQLServer查看分区表详细信息

SQL查看分区内记录个数，常规方法需要知道分区函数然后再显示，网上看到一个一句话显示的方法

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('yourtablename')--分区表名

and i.index_id in (0, 1)

order by p.partition_number

原文链接：https://blog.csdn.net/w2ndong/article/details/79494609

执行效果如下

其它方法

select count(1) ,$PARTITION.WorkDatePFN(workdate) from imgfile group by $PARTITION.WorkDatePFN(workdate)

查看分区记录数

select workdate ,$PARTITION.WorkDatePFN(workdate) from imgfile

查看记录所在分区

dbcc shrinkfile(N'yxfile001',1)

收缩分区文件yxfile001为1M

select * from sysfiles

查询数据库文件以及日志文件的相关信息（文件组、当前文件大小、文件最大值、文件增长设置、文件逻辑名、文件路径）

exec master.dbo.xp_fixeddrives

查询各个磁盘分区的剩余空间

exec sp_spaceused

查询当前数据库的磁盘使用情况

select * from sys.partition_range_values

可以查询到分区号

巴特西

SQLServer查看分区表详细信息

最新文章

热门文章