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...
>|||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 ne
ed 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...[vbcol=seagreen]
> 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:
>|||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/subscript...ault.aspx#notif
ications
<http://msdn.microsoft.com/subscript...ps/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/subscript...rt/default.aspx>.
========================================
==========
This posting is provided "AS IS" with no warranties, and confers no rights.