[SQL SERVER][TSQL] 查询Partition Table 相关资讯

整理一些自己查询partition table 相关资讯所使用的TSQL

 

1.判断资料表是否已分割

SELECT *

FROM sys.partitions AS p

JOIN sys.tables AS t

ON   p.object_id = t.object_id

WHERE p.partition_id IS  NOT  NULL

AND t.name = 'pt_test' —- your table name

 

 

2.取得某一资料的资料分割编号

--PF_test_SaleTime = your partition function

SELECT $PARTITION.PF_test_SaleTime ( '1990-01-01' ) ;

 

 

 

3.取得每个资料分割笔数(is not null)

SELECT $PARTITION.PF_test_SaleTime(SaleTime) AS Partition,

COUNT (*) AS [ COUNT ] FROM pt_test

GROUP  BY $PARTITION.PF_test_SaleTime(SaleTime)

ORDER  BY Partition ;

 

 

4.取得partition table 分割界限值

--pt_test = your partition table name

SELECT t.name AS TableName, i.name AS IndexName,r. value  AS BoundaryValue , p.partition_number, p.partition_id, i.data_space_id, f.function_id, f.type_desc, r.boundary_id

FROM sys.tables AS t

JOIN sys.indexes AS i

    ON t.object_id = i.object_id

JOIN sys.partitions AS p

    ON i.object_id = p.object_id AND i.index_id = p.index_id

JOIN   sys.partition_schemes AS s

    ON i.data_space_id = s.data_space_id

JOIN sys.partition_functions AS f

    ON s.function_id = f.function_id

LEFT  JOIN sys.partition_range_values ​​AS r

    ON f.function_id = r.function_id and r.boundary_id = p.partition_number

WHERE t.name = 'pt_test'  AND i.type <= 1

ORDER  BY p.partition_number;

 

 

5.取得partition column

SELECT t.object_id AS Object_ID, t.name AS TableName, ic.column_id as PartitioningColumnID, c.name AS PartitioningColumnName

FROM sys.tables AS t

JOIN sys.indexes AS i

    ON t.object_id = i.object_id

JOIN sys.columns AS c

    ON t.object_id = c.object_id

JOIN sys.partition_schemes AS ps

    ON ps.data_space_id = i.data_space_id

JOIN sys.index_columns AS ic

    ON ic.object_id = i.object_id AND ic.index_id = i.index_id AND ic.partition_ordinal > 0

WHERE t.name = 'pt_test'

AND i.type <= 1

AND c.column_id = 1;

 

另一位网友方法

select 

OBJECT_NAME(i.object_id), c.name from sys.index_columns i join sys.columns c on i.object_id = c.object_id

and i.column_id = c.column_id where partition_ordinal = 1

 

 

 

6.取得某资料分割中的资料

--传回资料分割=2 相关资料

SELECT * FROM pt_test

WHERE $PARTITION.PF_test_SaleTime(SaleTime) = 2 ;

 

 

 

7.取得所有partition table的partition function 、scheme和column name

SELECT OBJECT_NAME(p.OBJECT_ID) TableName,    

c.name PartColumn,      

ps.name PartScheme,    

pf.name PartFunction 

FROM sys.data_spaces d JOIN      

sys.indexes i JOIN   

( SELECT  DISTINCT OBJECT_ID     

FROM sys.partitions       

WHERE partition_number > 1) p    

ON i.OBJECT_ID = p.OBJECT_ID      

ON d.data_space_id = i.data_space_id     

JOIN sys.partition_schemes ps ON d.data_space_id = ps.data_space_id    

JOIN sys.partition_functions pf ON ps.function_id = pf.function_id      

JOIN sys.index_columns ic ON i.index_id = ic.index_id AND i.OBJECT_ID = ic.OBJECT_ID    

JOIN sys.columns c ON c.OBJECT_ID = ic.OBJECT_ID AND c.column_id = ic.column_id

 

 

 

 

参考

Create Partitioned Tables and Indexes

$PARTITION (Transact-SQL)

转载：http://www.dotblogs.com.tw/ricochen/archive/2012/05/04/71971.aspx