在 SQL Server 数据库中查找分区架构定义
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/24878446/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Find Partition Schema Definitions in SQL Server Database
提问by Brittany F
I have access to a database and I need to know the Partition Scheme definitions in the database. i.e. I need to know the partition scheme name, which Partition function is it using, what file groups are the partitions assigned, etc...
我有权访问一个数据库，我需要知道数据库中的分区方案定义。即我需要知道分区方案名称，它使用的是哪个分区功能，分配的分区是什么文件组，等等...
For example someone creates a partition scheme as so (taken from msdn):
例如，有人创建了一个分区方案（取自 msdn）：
CREATE PARTITION SCHEME myRangePS1
AS PARTITION myRangePF1
TO (test1fg, test2fg, test3fg, test4fg);
Then I want the name: myRangePS1, the function: myRangePF1, and the partitions: (test1fg, test2fg, test3fg, test4fg), Whether it is partition ALL or not
然后我要名称：myRangePS1，函数：myRangePF1，以及分区：(test1fg, test2fg, test3fg, test4fg), 是否是分区ALL
How would I go about this using SQL statements only? I can query the names and some data about partitions by using the system view sys.partition_scheme, but it is not enough.
我将如何仅使用 SQL 语句来解决这个问题？我可以使用系统视图sys.partition_scheme查询分区的名称和一些数据，但这还不够。
The below shows a similar solution on finding the definition of Partition functions: http://social.msdn.microsoft.com/forums/sqlserver/en-US/d0ce92e3-bf48-455d-bd89-c334654d7e97/how-to-find-partition-function-text-applied-to-a-table
下面显示了查找分区函数定义的类似解决方案：http: //social.msdn.microsoft.com/forums/sqlserver/en-US/d0ce92e3-bf48-455d-bd89-c334654d7e97/how-to-find-分区函数文本应用到表
回答by Brittany F
I have modified knkarthick24's first query to show Partition function values associated to each file group:
我修改了 knkarthick24 的第一个查询以显示与每个文件组关联的分区函数值：
select distinct ps.Name AS PartitionScheme, pf.name AS PartitionFunction,fg.name AS FileGroupName, rv.value AS PartitionFunctionValue
from sys.indexes i
join sys.partitions p ON i.object_id=p.object_id AND i.index_id=p.index_id
join sys.partition_schemes ps on ps.data_space_id = i.data_space_id
join sys.partition_functions pf on pf.function_id = ps.function_id
left join sys.partition_range_values rv on rv.function_id = pf.function_id AND rv.boundary_id = p.partition_number
join sys.allocation_units au ON au.container_id = p.hobt_id
join sys.filegroups fg ON fg.data_space_id = au.data_space_id
where i.object_id = object_id('TableName')
This is the query I was looking for and I hope other people can make use of this!
这是我一直在寻找的查询，我希望其他人可以使用它！
回答by knkarthick24
Please try this query: 1)
请试试这个查询：1)
select ps.Name AS PartitionScheme, pf.name AS PartitionFunction,fg.name AS FileGroupName
from sys.indexes i
JOIN sys.partitions p ON i.object_id=p.object_id AND i.index_id=p.index_id
join sys.partition_schemes ps on ps.data_space_id = i.data_space_id
join sys.partition_functions pf on pf.function_id = ps.function_id
join sys.allocation_units au ON au.container_id = p.hobt_id
join sys.filegroups fg ON fg.data_space_id = au.data_space_id
where i.object_id = object_id('TableName')
or for more detailed information use the below query( SQL 2008 Internals Book)
或更多详细信息，请使用以下查询（SQL 2008 Internals Book）
2)
2)
SELECT
ISNULL(quotename(ix.name),'Heap') as IndexName
,ix.type_desc as type
,prt.partition_number
,prt.data_compression_desc
,ps.name as PartitionScheme
,pf.name as PartitionFunction
,fg.name as FilegroupName
,case when ix.index_id < 2 then prt.rows else 0 END as Rows
,au.TotalMB
,au.UsedMB
,case when pf.boundary_value_on_right = 1 then 'less than' when pf.boundary_value_on_right is null then '' else 'less than or equal to' End as Comparison
,fg.name as FileGroup
,rv.value
FROM sys.partitions prt
inner join sys.indexes ix
on ix.object_id = prt.object_id and
ix.index_id = prt.index_id
inner join sys.data_spaces ds
on ds.data_space_id = ix.data_space_id
left join sys.partition_schemes ps
on ps.data_space_id = ix.data_space_id
left join sys.partition_functions pf
on pf.function_id = ps.function_id
left join sys.partition_range_values rv
on rv.function_id = pf.function_id AND
rv.boundary_id = prt.partition_number
left join sys.destination_data_spaces dds
on dds.partition_scheme_id = ps.data_space_id AND
dds.destination_id = prt.partition_number
left join sys.filegroups fg
on fg.data_space_id = ISNULL(dds.data_space_id,ix.data_space_id)
inner join (select str(sum(total_pages)*8./1024,10,2) as [TotalMB]
,str(sum(used_pages)*8./1024,10,2) as [UsedMB]
,container_id
from sys.allocation_units
group by container_id) au
on au.container_id = prt.partition_id
WHERE prt.OBJECT_ID = object_id(N'dbo.test')
order by ix.type_desc;
