Determining Filegroup for a Table

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.

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.

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.

12 thoughts on “Determining Filegroup for a Table”

Thanks for the script, I’m working on a new server and needed to find the tables with partitions and their details. Great post. FYI … I made a second query based on this grouping by Schema, table, Partition scheme and partition function (added partition_function view) so I have a quick query to find all tables with Partitions and what Partition Scheme and P. Function they’re using.

This was super helpful – on the theme of adding completeness a trip over to sys.schemas is probably in order as well :-). I’m tweaking it to add that now. – Merrill Aldrich (after forgetting my old Disqus credentials)