This might sound simple to SQL experts, Apart from the query I feel that the first paragraph of this post is useful to all. So read on.
The file group information of both an Index and a Table are stored in the sys.indexes metadata 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 sorts the physical data pages using the clustered index key and the data pages are made part of the clustered index i.e. the leaf nodes of the clustered index contain the physical table data. So the file group of the clustered index is the file group of the table.
What if there is no clustered index on 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
6 responses to “How to get the File Group of a Table or an Index?”
How to get the File Group of a Table or an Index? « ranjithk’s blog…
Kudos for a great Sql Server article – Trackback from SqlServerKudos…
How to get the File Group of a Table or an Index? « ranjithk’s blog…
Thank you for submitting this cool story – Trackback from DotNetShoutout…
Cheers for your details about this. You helped all of us fully grasp some things I failed to before.
Good article !!!
nice article
The query doesn’t seems to work for the cluster index of portioned tables.