Partitions and FileGroups

I'm writing a SP to automate the creation & maintenance of Partitions and
Filegroups. Every 1st of a month, I have to create a new partition to move
all the previous months data to a new partition and at the end of the year I
have to consoldiate all the years partition into just one Partition.
I have automated the creation part and now I'm in the middle of automating
the deletion of the 11 partitions and moving the data (from all these 11
partitions) into the 12th partition.
Having moved these 11 partitions, I would like to drop the files and
filegroups associated to these partitions. To accomplish this, I need to find
out which partitions were mapped to which filegroups. Is there a way to find
out?
Thanks,
rgn
I think that the following query should get you what you need. There's a
mapping table called sys.destination_data_spaces that maps the partition
scheme's "data space ID" to the data space of the filegroup:
select P.[partition_id], P.[object_id], P.[index_id], P.[partition_number],
FG.name 'filegroup_name'
from sys.partitions P
join sys.indexes I on P.index_id = I.index_id and P.object_id =
I.object_id
join sys.partition_schemes S on I.data_space_id = S.data_space_id
join sys.destination_data_spaces DDS on S.data_space_id =
DDS.partition_scheme_id
join sys.filegroups FG on DDS.data_space_id = FG.data_space_id
order by P.object_id, P.index_Id, P.partition_number
"rgn" <rgn@.discussions.microsoft.com> wrote in message
news:F4D34E12-ED79-46FF-A067-01067410BBF6@.microsoft.com...
> I'm writing a SP to automate the creation & maintenance of Partitions and
> Filegroups. Every 1st of a month, I have to create a new partition to move
> all the previous months data to a new partition and at the end of the year
> I
> have to consoldiate all the years partition into just one Partition.
> I have automated the creation part and now I'm in the middle of automating
> the deletion of the 11 partitions and moving the data (from all these 11
> partitions) into the 12th partition.
> Having moved these 11 partitions, I would like to drop the files and
> filegroups associated to these partitions. To accomplish this, I need to
> find
> out which partitions were mapped to which filegroups. Is there a way to
> find
> out?
> Thanks,
> rgn