Home > SQL Server > How to get the File Group of a Table or an Index?

How to get the File Group of a Table or an Index?

 

The file group information of both an Index and a Table are stored in the sys.indexes table. You might wonder how the tables file group is stored in sys.indexes metadata table. It is because when ever a clustered index is created on a table in SQL Server the physical data pages of the table will be made part of the clustered index i.e. the leaf nodes of the clustered index contain the physical table data. So the clustered index file group is the file group of the table.

If there is no clustered index on the table then it is represented as a HEAP with index_id equal to ZERO in sys.indexes table. 

At any point a clustered index with index_id equal to 1 or a heap with index_id equal to ZERO exists for a table in sys.indexes table.  All non clustered indexes will have index_id greater than 1.

Below query gets the file group of the table Employee in HumanResources schema of AdventureWorks database.

 SELECT d.name AS FileGroup
 FROM sys.filegroups d
 JOIN sys.indexes i
   ON i.data_space_id = d.data_space_id
 JOIN sys.tables t
   ON t.object_id = i.object_id
WHERE i.index_id<2                     -- could be heap or a clustered table
 AND t.name= 'Employee'
 AND t.schema_id = schema_id('HumanResources')

And below query gets the file group of the index ‘AK_Employee_rowguid’ on Employee table in HumanResources schema of AdventureWorks database.

 SELECT d.name AS FileGroup
 FROM sys.filegroups d
 JOIN sys.indexes i
   ON i.data_space_id = d.data_space_id
 JOIN sys.tables t
   ON t.object_id = i.object_id
WHERE i.name = 'AK_Employee_rowguid'        
 AND t.name= 'Employee'
 AND t.schema_id = schema_id('HumanResources')

Please note that schema_id is important otherwise both the queries might return multiple results if you have same table name on different schemas.

Hope it helps

🙂 ranjith 🙂

Advertisements
  1. No comments yet.
  1. April 23, 2010 at 8:54 am
  2. April 23, 2010 at 10:26 am

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: