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

  • Question

  • 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

     

    Friday, October 6, 2006 6:35 PM

Answers

  • 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

     

     

     

    Saturday, October 7, 2006 3:33 PM