-- 获取分区表以及每个分区的数据存储信息
- SELECT
- T.NAME AS [TABLE_NAME]
- ,PRV.BOUNDARY_ID -- 分区号
- ,P.ROWS -- 采样行数/分区
- ,PRV.VALUE AS [RANGE_VALUE] -- 分区点
- ,P.DATA_COMPRESSION_DESC -- 是否使用压缩算法,压缩类型
- ,PF.FUNCTION_ID -- 分区函数ID
- ,PF.FANOUT
- ,PF.CREATE_DATE -- 创建时间
- ,PF.MODIFY_DATE -- 修改时间
- ,PS.NAME AS [PS_NAME] -- 分区架构名称
- ,PF.NAME AS [PF_NAME] -- 分区函数名称
- FROM
- SYS.TABLES T
- INNER JOIN
- SYS.INDEXES IDX
- ON
- T.OBJECT_ID = IDX.OBJECT_ID
- INNER JOIN
- SYS.DATA_SPACES DS
- ON
- IDX.DATA_SPACE_ID = DS.DATA_SPACE_ID AND DS.TYPE='PS' AND IDX.INDEX_ID < 2 -- 取表
- INNER JOIN
- SYS.PARTITION_SCHEMES PS
- ON
- DS.NAME = PS.NAME
- INNER JOIN
- SYS.PARTITION_FUNCTIONS PF
- ON
- PS.FUNCTION_ID = PF.FUNCTION_ID
- INNER JOIN
- SYS.PARTITION_RANGE_VALUES PRV
- ON
- PF.FUNCTION_ID = PRV.FUNCTION_ID
- INNER JOIN
- SYS.PARTITIONS P
- ON
- P.OBJECT_ID = T.OBJECT_ID AND P.PARTITION_NUMBER = PRV.BOUNDARY_ID AND P.INDEX_ID < 2
- WHERE
- T.OBJECT_ID = OBJECT_ID('dbo.tablename')
- ORDER BY
- PF.FUNCTION_ID ASC, T.OBJECT_ID ASC, PRV.VALUE ASC
转载于:https://blog.51cto.com/greece760/785505