filegroup and table

In SQL studio, when examining a table properties, we can see on which
filgroup the table is,
Which catalog view/DM /SP can give the same info "on which filegroup my
table is"?Check out the following:
sys.indexes has a column data_space_id.
sys.filegroups also has that column.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"SalamElias" <eliassal@.online.nospam> wrote in message
news:DA0B190B-B9C8-4378-8F44-9F6274830648@.microsoft.com...
> In SQL studio, when examining a table properties, we can see on which
> filgroup the table is,
> Which catalog view/DM /SP can give the same info "on which filegroup my
> table is"?|||Thanks, but I am asking about Tables not indexes. Correct, the sys.indexes
has the field but not sys.tables.
Would you please explaing if your answer refers to make a join to get the
property in someway.
thanks again
"Tibor Karaszi" wrote:
> Check out the following:
> sys.indexes has a column data_space_id.
> sys.filegroups also has that column.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "SalamElias" <eliassal@.online.nospam> wrote in message
> news:DA0B190B-B9C8-4378-8F44-9F6274830648@.microsoft.com...
> > In SQL studio, when examining a table properties, we can see on which
> > filgroup the table is,
> > Which catalog view/DM /SP can give the same info "on which filegroup my
> > table is"?
>|||Thanks, I am talking about tables not indexes. You are correct, sys.indexes,
sys.database_files and sys.filegroups contain this field but not sys.tables.
If you ment to make a join to retreieve the value for a table,would you
please indicate how, thanks again
"SalamElias" wrote:
> In SQL studio, when examining a table properties, we can see on which
> filgroup the table is,
> Which catalog view/DM /SP can give the same info "on which filegroup my
> table is"?|||A table is also represented in sys.indexes. Either with indid 1 or 0. You need to look in
sys.indexes because this show the physical location for a heap or a b-tree, sys.tables don't give
you that information.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"SalamElias" <eliassal@.online.nospam> wrote in message
news:A42C7C88-2A2D-4A92-BA3B-E8C6E36D3F3B@.microsoft.com...
> Thanks, but I am asking about Tables not indexes. Correct, the sys.indexes
> has the field but not sys.tables.
> Would you please explaing if your answer refers to make a join to get the
> property in someway.
> thanks again
> "Tibor Karaszi" wrote:
>> Check out the following:
>> sys.indexes has a column data_space_id.
>> sys.filegroups also has that column.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "SalamElias" <eliassal@.online.nospam> wrote in message
>> news:DA0B190B-B9C8-4378-8F44-9F6274830648@.microsoft.com...
>> > In SQL studio, when examining a table properties, we can see on which
>> > filgroup the table is,
>> > Which catalog view/DM /SP can give the same info "on which filegroup my
>> > table is"?
>>|||Hello Salam,
I agree with Tibor that sys.indexes also has information of table. When
index_id =0, it is heap, and index_id=1, it is clustered index. Please see
following link in BOL for details:
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/066bd9ac-6554-4297-88fe-
d740de1f94a8.htm
Therefore, you could use the following information to get the filgroup
information of tables.
select tbl.name,
dsidx.name, dsidx.type
FROM
sys.tables AS tbl
INNER JOIN sys.indexes AS idx ON idx.object_id = tbl.object_id and
idx.index_id < 2
LEFT OUTER JOIN sys.data_spaces AS dstext ON tbl.lob_data_space_id =dstext.data_space_id
LEFT OUTER JOIN sys.data_spaces AS dsidx ON dsidx.data_space_id =idx.data_space_id
In addtion, if you use profiler to trace the query when you show the
properties of a table, you shall see the query also references
sys.indexes/sys.tables/sys.data_space.
If anything is unclear, please feel free to let's know. We look forward to
your reply. Thanks.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Community Support
==================================================Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications
<http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx>.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
<http://msdn.microsoft.com/subscriptions/support/default.aspx>.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.