2005 Table Partitioning

Hi - can anyone tell me how I can query which filegroup a particular
partition number belongs to'
For example I have a date range partition function & the query:
SELECT PartitionNo = $partition.DateRangePFN ('20070401')
Tells me this date would be placed in partition number 2 which should be in
a Filegroup called "April" but I just wanted to check that partition 2 is
actually mapped to filegroup April & I can't see how to do it!!
Thanks!!
MikeOn Jun 8, 3:56 pm, "Michael Knee" <mike...@.hotmail.com> wrote:
> Hi - can anyone tell me how I can query which filegroup a particular
> partition number belongs to'
> For example I have a date range partition function & the query:
> SELECT PartitionNo = $partition.DateRangePFN ('20070401')
> Tells me this date would be placed in partition number 2 which should be in
> a Filegroup called "April" but I just wanted to check that partition 2 is
> actually mapped to filegroup April & I can't see how to do it!!
> Thanks!!
> Mike
select
prv.boundary_id,
prv.value,
fg.name
from
sys.partition_range_values prv
inner join
sys.partition_functions pf on prv.function_id = pf.function_id
inner join
sys.destination_data_spaces dds on dds.destination_id =prv.boundary_id
inner join
sys.filegroups fg on fg.data_space_id = dds.data_space_id
where
pf.name = 'PF_DateRange'
and
prv.boundary_id = $partition.PF_DateRange('20050814')
See http://weblogs.sqlteam.com/dmauri/archive/2005/08/20/7593.aspx|||Exactly what I was after & excellent blog link as well - Thank You!
Mike
"M A Srinivas" <masri999@.gmail.com> wrote in message
news:1181303483.375856.68170@.r19g2000prf.googlegroups.com...
> On Jun 8, 3:56 pm, "Michael Knee" <mike...@.hotmail.com> wrote:
>> Hi - can anyone tell me how I can query which filegroup a particular
>> partition number belongs to'
>> For example I have a date range partition function & the query:
>> SELECT PartitionNo = $partition.DateRangePFN ('20070401')
>> Tells me this date would be placed in partition number 2 which should be
>> in
>> a Filegroup called "April" but I just wanted to check that partition 2 is
>> actually mapped to filegroup April & I can't see how to do it!!
>> Thanks!!
>> Mike
> select
> prv.boundary_id,
> prv.value,
> fg.name
> from
> sys.partition_range_values prv
> inner join
> sys.partition_functions pf on prv.function_id = pf.function_id
> inner join
> sys.destination_data_spaces dds on dds.destination_id => prv.boundary_id
> inner join
> sys.filegroups fg on fg.data_space_id = dds.data_space_id
> where
> pf.name = 'PF_DateRange'
> and
> prv.boundary_id = $partition.PF_DateRange('20050814')
>
> See http://weblogs.sqlteam.com/dmauri/archive/2005/08/20/7593.aspx
>