Posted by traceable1 on 09/28/07 15:55

Thank you so much!
i have the query:

select *
from sys.data_spaces ds,
sys.allocation_units au,
sys.partitions p,
sysindexes si
WHERE
si.name = @indname
AND si.id = p.object_id
AND si.indid = p.index_id
AND p.hobt_id = au.container_id
AND au.data_space_id = ds.data_space_id


If this comes up with nothing, it is not a partitioned index.
If it is a partitioned index, i can get the file group names from this
and only optimize the PRIMARY and 2007 partitions.

thank you!!





On Sep 14, 4:24 pm, Erland Sommarskog <esq...@sommarskog.se> wrote:
> traceable1 (thham...@gmail.com) writes:
> > So, is the groupid = data_space_id?
>
> The relation of sys.data_spaces, sys.destination_data_spaces and
> sys.filegroups is a bit complicated. A filegroup is a data space,
> but the opposite does not apply.
>
> > Then is the data_space_id = partition_number? I'm still having
> > trouble getting from allocation_units to partitions.
>
> No, the data_space_id is not thepartitionnumber. Instead you join
> sys.allocation_units to sys.partitions over the container_id,
> and to make it even more complicated, you join to different columns
> in sys.partitions depending on type of allocation unit. In sys.partitions
> you find thepartitionnumber.
>
> I'm sorry that I don't simply give you a query, but I don't have any
> multiple-filegroup database set up, so I can't test.
>
> --
> Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se
>
> Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books...
> Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]