Finding the file that a partition is stored in

I've looked through the various sys views regarding partitions but I can't
seem to find a way of determining what physical file or filegroup an
existing partition is stored on. Even the original create script for a
partition can't be relied on as partition ranges can be merged or split.

Thanks,

Joe Lax

The catalog view to query is sys. destination_data_spa
ces. A query similar to
the following should do:

select ds.name
from sys. destination_data_spa
ces dds join sys.data_spaces ds
on dds.data_space_id = ds.data_space_id
join sys.indexes i
on dds.partition_scheme_id = i.data_space_id
where dds.destination_id = $partition. PartitionFunction('y
our value')
and i.object_id = object_id('table name')
and i.index_id in (0,1)

Linchi

"Joe" wrote:

> I've looked through the various sys views regarding partitions but I can't
> seem to find a way of determining what physical file or filegroup an
> existing partition is stored on. Even the original create script for a
> partition can't be relied on as partition ranges can be merged or split.
>
> Thanks,
>
> Joe Lax
>
>
>