Filegroups Properties

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, EnglandYes, 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|||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|||For instance EM, right-click the table. Or QA, the same.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"new_sql_dba" <newsqldba@.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, Englandsql