[MSSQL] 如何從partition value找到使用哪一個filegroup

找了老半天，總算找到了，還真是困難。
http://social.msdn.microsoft.com/Forums/en/sqldatabaseengine/thread/0bb0ba4e-fc06-4d8b-a79d-fb275fac3da1

節錄裡面的SQL

@var int
set @var = object_id('dbo.t1')
select main.tabname, main.partition_id, main.partition_number, main.rows, main.name, main.data_space_id,
main.partition_scheme_id, main.partid, main.function_id, part.value, part.partition_id
from
(select object_name(a.object_id) tabname, a.partition_id, a.partition_number, a.rows,
c.name, c.data_space_id, d.partition_scheme_id, d.destination_id as partid, e.function_id
from sys.partitions a inner join sys.allocation_units b on a.hobt_id = b.container_id
inner join sys.data_spaces c on b.data_space_id = c.data_space_id
inner join sys.destination_data_spaces d on c.data_space_id = d.data_space_id
inner join sys.partition_schemes e on d.partition_scheme_id = e.data_space_id
where a.object_id = @var) main
left join
(select a.function_id, b.value, case when a.boundary_value_on_right = 0 then b.boundary_id else b.boundary_id + 1 end partition_id
from sys.partition_functions a inner join sys.partition_range_values b on a.function_id = b.function_id) part
on main.function_id = part.function_id and main.partition_number = part.partition_id
order by main.partid


為了這個我花了三四天吧....希望對於需要的人有幫助。