查询分区和索引：
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是分区表。