Have you ever needed to figure out which filegroup your tables are located within?  If you had to do this, you might think it’s as easy joining sys.tables to sys.filegroups.  But its a little more complicated than that because tables aren’t stored in filegroups.

It’s The Indexes

It’s the indexes for a table that determine where the data is located.  As a result, in order to determine the filegroup for a table, you need to look at the indexes.  If this doesn’t make sense, check out Rob Farley’s (Blog | @Rob_Farley) blog post “Table? No such thing…”

Anyways, to find out where a table (and it’s indexes) are located within a database you will need to look at sys.indexes and join that to sys.tables and sys.filegroups.  The resulting query is provided in Listing 1.  For added benefit, sys.partitions is used to add in the row count with the FORMAT function to improve readability of the number of rows.  If you run this on versions prior to SQL Server 2012 the FORMAT function will need to be removed.

-- Listing 1. Query to determine table filegroup by index
SELECT t.name AS table_name
,i.index_id
,i.name AS index_name
,ds.name AS filegroup_name
,FORMAT(p.rows, '#,###') AS rows
FROM sys.tables t
INNER JOIN sys.indexes i ON t.object_id=i.object_id
INNER JOIN sys.filegroups ds ON i.data_space_id=ds.data_space_id
INNER JOIN sys.partitions p ON i.object_id=p.object_id AND i.index_id=p.index_id
ORDER BY t.name, i.index_id

And it’s the Partitions

In the comments, Stefan reminded me that you also have to consider partitions.  This is because when a table and/or it’s indexes are partitioned then things change yet again.  For partitioned indexes, the index no longer determines where the data is located, instead that is defined by the partitioning scheme.  To find where the partition is located, the catalog views sys.partition_schemes and sys.destination_data_spaces need to be added to the query, provided in Listing 2, which can then be joined to sys.filegroups.

-- Listing 2. Query to determine table filegroup by index and partition
SELECT t.name AS table_name
,i.index_id
,i.name AS index_name
,p.partition_number
,fg.name AS filegroup_name
,FORMAT(p.rows, '#,###') AS rows
FROM sys.tables t
INNER JOIN sys.indexes i ON t.object_id = i.object_id
INNER JOIN sys.partitions p ON i.object_id=p.object_id AND i.index_id=p.index_id
LEFT OUTER JOIN sys.partition_schemes ps ON i.data_space_id=ps.data_space_id
LEFT OUTER JOIN sys.destination_data_spaces dds ON ps.data_space_id=dds.partition_scheme_id AND p.partition_number=dds.destination_id
INNER JOIN sys.filegroups fg ON COALESCE(dds.data_space_id, i.data_space_id)=fg.data_space_id

Wrap-Up

There’s nothing too earth shattering in this post, just a quick share of a script I often use.  As a final tip, you might be tempted to write the query above using systables, sysfilegroups and/or sysindexes.  While tempting, try to avoid these compatibility views since they have been deprecated and will be removed from a future version of SQL Server.  For more information on this check out my blog series Lost in Translation – Deprecated System Tables.