当前位置：首页 > news >正文

sql server 分区表查询

news 来源：原创 2024/9/20 14:46:17


----查询是要指定的数据库下查询，否则查询不到信息
--查看是否是分区表
SELECT SCHEMA_NAME(t.schema_id) AS SchemaName, *   
FROM sys.tables AS t   
JOIN sys.indexes AS i   ON t.[object_id] = i.[object_id]   
JOIN sys.partition_schemes ps   ON i.data_space_id = ps.data_space_id   
WHERE t.name = 'table_name';   
GO
--确定已分区表的边界值
SELECT SCHEMA_NAME(t.schema_id) AS SchemaName, t.name AS TableName, i.name AS IndexName, p.partition_number, p.partition_id, i.data_space_id, f.function_id, f.type_desc, r.boundary_id, r.value AS BoundaryValue   
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 = 'table_name' AND i.type <= 1  
ORDER BY SchemaName, t.name, i.name, p.partition_number;--查看分区列
SELECT   t.[object_id] AS ObjectID, SCHEMA_NAME(t.schema_id) AS SchemaName, t.name AS TableName   , ic.column_id AS PartitioningColumnID   , c.name AS PartitioningColumnName, i.name as IndexName
FROM sys.tables AS t   
JOIN sys.indexes AS i   ON t.[object_id] = i.[object_id]   AND i.[type] <= 1 -- clustered index or a heap   
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 >= 1 -- because 0 = non-partitioning column   
JOIN sys.columns AS c   ON t.[object_id] = c.[object_id]   AND ic.column_id = c.column_id   
WHERE t.name = 'table_name';   
GO---每个分区中可能的值范围的行SELECT SCHEMA_NAME(t.schema_id) AS SchemaName, t.name AS TableName, i.name AS IndexName, p.partition_number AS PartitionNumber, f.name AS PartitionFunctionName, p.rows AS Rows, rv.value AS BoundaryValue, 
CASE WHEN ISNULL(rv.value, rv2.value) IS NULL THEN 'N/A' 
ELSECASE WHEN f.boundary_value_on_right = 0 AND rv2.value IS NULL THEN '>=' WHEN f.boundary_value_on_right = 0 THEN '>' ELSE '>=' END + ' ' + ISNULL(CONVERT(varchar(64), rv2.value), 'Min Value') + ' ' + CASE f.boundary_value_on_right WHEN 1 THEN 'and <' ELSE 'and <=' END + ' ' + ISNULL(CONVERT(varchar(64), rv.value), 'Max Value') 
END AS TextComparison
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  
LEFT JOIN sys.partition_range_values AS rvON f.function_id = rv.function_idAND p.partition_number = rv.boundary_id     
LEFT JOIN sys.partition_range_values AS rv2ON f.function_id = rv2.function_idAND p.partition_number - 1= rv2.boundary_id
WHERE t.name = 'table_name'AND i.type <= 1 
ORDER BY t.name, p.partition_number;

参考：
创建分区的表和索引 - SQL Server, Azure SQL Database, Azure SQL Managed Instance | Microsoft Learn