How to find filegroups for a given Table and Table's indexes

Hi

I am using SQL Server 2005 Developer Edition.

I want a list of the following things from the database: -

Table Name , FileGroup Table resides on

Table Name, Index Name, FileGroup index resides on

To put it simply, consider the following example:-

Lets say I have a table XYZ in my database created on Filegroup F1. It has a PK PK1 nonclustered index on Filegroup F2.

List1

-

XYZ F1

List2

XYZ PK1 F2

Please do not tell me of sp_help <table> option

Regards

Imtiaz

For the table XYZ

select OBJECT_NAME(i.object_id) [Table_Name], ds.name [Filegroup_Name] from sys.indexes i join sys.filegroups ds on (ds.data_space_id=i.data_space_id) where object_name(i.object_id) = 'XYZ' and i.index_id=0

--This will work when the table does not have any clustered index on it.

For the non clustered index,

select OBJECT_NAME(i.object_id) [Table_Name], i.name [Index_Name], ds.name [Filegroup_name] from sys.indexes i join sys.filegroups ds on (ds.data_space_id=i.data_space_id) where object_name(i.object_id) = 'XYZ' and i.name='PK1'

For more such catalog view queries

http://msdn2.microsoft.com/en-us/library/ms345522.aspx