[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
SELECT SCHEMA_NAME([schema_id]) AS [schema_name]
,t.[name] AS [table_name]
,i.[name] AS [index_name]
,i.[type_desc] AS [index_type]
,ps.[name] AS [partition_scheme]
,pf.[name] AS [partition_function]
,p.[partition_number]
,r.[value] AS [current_partition_range_boundary_value]
,p.[rows] AS [partition_rows]
,p.[data_compression_desc]
FROM sys.tables t
INNER JOIN sys.partitions p ON p.[object_id] = t.[object_id]
INNER JOIN sys.indexes i ON p.[object_id] = i.[object_id]
AND p.[index_id] = i.[index_id]
INNER JOIN sys.data_spaces ds ON i.[data_space_id] = ds.[data_space_id]
INNER JOIN sys.partition_schemes ps ON ds.[data_space_id] = ps.[data_space_id]
INNER JOIN sys.partition_functions pf ON ps.[function_id] = pf.[function_id]
LEFT JOIN sys.partition_range_values AS r ON pf.[function_id] = r.[function_id]
AND r.[boundary_id] = p.[partition_number]
GROUP BY SCHEMA_NAME([schema_id])
,t.[name]
,i.[name]
,i.[type_desc]
,ps.[name]
,pf.[name]
,p.[partition_number]
,r.[value]
,p.[rows]
,p.[data_compression_desc]
ORDER BY SCHEMA_NAME([schema_id])
,t.[name]
,i.[name]
,p.[partition_number];
參考
