查看分区表中分区详细信息

 

查询分区和索引：

 1 SELECT OBJECT_NAME(p.object_id) AS ObjectName,
 2       i.name                   AS IndexName,
 3       p.index_id               AS IndexID,
 4       ds.name                  AS PartitionScheme,   
 5       p.partition_number       AS PartitionNumber,
 6       fg.name                  AS FileGroupName,
 7       prv_left.value           AS LowerBoundaryValue,
 8       prv_right.value          AS UpperBoundaryValue,
 9       CASE pf.boundary_value_on_right
10             WHEN 1 THEN 'RIGHT'
11             ELSE 'LEFT' END    AS Range,
12       p.rows AS Rows
13 FROM sys.partitions                  AS p
14 JOIN sys.indexes                     AS i
15       ON i.object_id = p.object_id
16       AND i.index_id = p.index_id
17 JOIN sys.data_spaces                 AS ds
18       ON ds.data_space_id = i.data_space_id
19 JOIN sys.partition_schemes           AS ps
20       ON ps.data_space_id = ds.data_space_id
21 JOIN sys.partition_functions         AS pf
22       ON pf.function_id = ps.function_id
23 JOIN sys.destination_data_spaces     AS dds2
24       ON dds2.partition_scheme_id = ps.data_space_id 
25       AND dds2.destination_id = p.partition_number
26 JOIN sys.filegroups                  AS fg
27       ON fg.data_space_id = dds2.data_space_id
28 LEFT JOIN sys.partition_range_values AS prv_left
29       ON ps.function_id = prv_left.function_id
30       AND prv_left.boundary_id = p.partition_number - 1
31 LEFT JOIN sys.partition_range_values AS prv_right
32       ON ps.function_id = prv_right.function_id
33       AND prv_right.boundary_id = p.partition_number 
34 WHERE
35       object_name(p.object_id)='tb_test'
36 UNION ALL
37 SELECT
38       OBJECT_NAME(p.object_id)    AS ObjectName,
39       i.name                      AS IndexName,
40       p.index_id                  AS IndexID,
41       NULL                        AS PartitionScheme,
42       p.partition_number          AS PartitionNumber,
43       fg.name                     AS FileGroupName,  
44       NULL                        AS LowerBoundaryValue,
45       NULL                        AS UpperBoundaryValue,
46       NULL                        AS Boundary, 
47       p.rows                      AS Rows
48 FROM sys.partitions     AS p
49 JOIN sys.indexes        AS i
50       ON i.object_id = p.object_id
51       AND i.index_id = p.index_id
52 JOIN sys.data_spaces    AS ds
53       ON ds.data_space_id = i.data_space_id
54 JOIN sys.filegroups           AS fg
55       ON fg.data_space_id = i.data_space_id
56 WHERE
57       object_name(p.object_id)='tb_test'
58 ORDER BY
59       ObjectName,
60       IndexID,
61       PartitionNumber

 备注：其中tb_test是分区表。

 

posted @ 2015-12-03 17:23  cctext  阅读(881)  评论(0)  编辑  收藏  举报