"new_sql_dba" wrote:

> Hello
>
> I am fairly new to sql server and need some help:-
>
> We have a database with several file groups e.g primarry,
> data1,data2,data3,index1,index2,index3 etc.   Each file group has one file
> associated with this and they are on seperated disks.
>
> Is there any sql code that will display each table or index and its
> allocated filegroup?
>
> Basically I want to get a list of what is allocated to each file group, it
> will take me hours within enterprise manager looking at each table.
>
> Thanks for your help.
>
> Jason - Hull, England

"Ranga" wrote:

> Yes, See sysindexes system table....there is a column groupid, join this with
> sysfilegroups... If a clustered index exists in a filegroup then it means the
> table data also exists in the same filegroup since clustered index and table
> data are the same...If a table does not have a clustered index, we will not
> know which filegroup it exists in any system table, but you can find out if
> you script the table...
>
> HTH..
>
> "new_sql_dba" wrote:
>
> > Hello
> >
> > I am fairly new to sql server and need some help:-
> >
> > We have a database with several file groups e.g primarry,
> > data1,data2,data3,index1,index2,index3 etc.   Each file group has one file
> > associated with this and they are on seperated disks.
> >
> > Is there any sql code that will display each table or index and its
> > allocated filegroup?
> >
> > Basically I want to get a list of what is allocated to each file group, it
> > will take me hours within enterprise manager looking at each table.
> >
> > Thanks for your help.
> >
> > Jason - Hull, England

"new_sql_dba" <newsql***@discussions.microsoft.com> wrote in message
news:29285C84-0A23-4276-B6C2-ADF5B665F41C@microsoft.com...
> Thanks for your help
>
> How do you script the table?
>
>
> "Ranga" wrote:
>
>> Yes, See sysindexes system table....there is a column groupid, join this with
>> sysfilegroups... If a clustered index exists in a filegroup then it means the
>> table data also exists in the same filegroup since clustered index and table
>> data are the same...If a table does not have a clustered index, we will not
>> know which filegroup it exists in any system table, but you can find out if
>> you script the table...
>>
>> HTH..
>>
>> "new_sql_dba" wrote:
>>
>> > Hello
>> >
>> > I am fairly new to sql server and need some help:-
>> >
>> > We have a database with several file groups e.g primarry,
>> > data1,data2,data3,index1,index2,index3 etc.   Each file group has one file
>> > associated with this and they are on seperated disks.
>> >
>> > Is there any sql code that will display each table or index and its
>> > allocated filegroup?
>> >
>> > Basically I want to get a list of what is allocated to each file group, it
>> > will take me hours within enterprise manager looking at each table.
>> >
>> > Thanks for your help.
>> >
>> > Jason - Hull, England