Determining Filegroup for a Table

Posted by & filed under , , , .

tableHave 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 OBJECT_SCHEMA_NAME(t.object_id) AS schema_name
,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 OBJECT_SCHEMA_NAME(t.object_id) AS schema_name
,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.

  • Stefan

    If your are using mulitple partitions, the query won’t return the correct result.

    I’m using the following query ( SQL 2008 Internals Book)

    SELECT
    ISNULL(quotename(ix.name),’Heap’) as IndexName
    ,ix.type_desc as type
    ,prt.partition_number
    ,prt.data_compression_desc
    ,ps.name as PartitionScheme
    ,pf.name as PartitionFunction
    ,fg.name as FilegroupName
    ,case when ix.index_id < 2 then prt.rows else 0 END as Rows
    ,au.TotalMB
    ,au.UsedMB
    ,case when pf.boundary_value_on_right = 1 then 'less than' when pf.boundary_value_on_right is null then '' else 'less than or equal to' End as Comparison
    ,fg.name as FileGroup
    ,rv.value
    FROM sys.partitions prt
    inner join sys.indexes ix
    on ix.object_id = prt.object_id and
    ix.index_id = prt.index_id
    inner join sys.data_spaces ds
    on ds.data_space_id = ix.data_space_id
    left join sys.partition_schemes ps
    on ps.data_space_id = ix.data_space_id
    left join sys.partition_functions pf
    on pf.function_id = ps.function_id
    left join sys.partition_range_values rv
    on rv.function_id = pf.function_id AND
    rv.boundary_id = prt.partition_number
    left join sys.destination_data_spaces dds
    on dds.partition_scheme_id = ps.data_space_id AND
    dds.destination_id = prt.partition_number
    left join sys.filegroups fg
    on fg.data_space_id = ISNULL(dds.data_space_id,ix.data_space_id)
    inner join (select str(sum(total_pages)*8./1024,10,2) as [TotalMB]
    ,str(sum(used_pages)*8./1024,10,2) as [UsedMB]
    ,container_id
    from sys.allocation_units
    group by container_id) au
    on au.container_id = prt.partition_id
    WHERE prt.OBJECT_ID = object_id(N'dbo.test')
    order by ix.type_desc;

    • StrateSQL

      Thanks, Stefan. I hadn’t remembered to consider partitions when I wrote this the other day. I’ve updated the post to include this.

  • Pingback: Strate SQL Ask the Unicorn: Why is it Important to Know Tables in Filegroups? | Strate SQL()

  • Merrill Aldrich

    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)

    • StrateSQL

      Ah definitely! I’ll have to update my script. Thanks, Merrill.

  • Pingback: Find Partitioned Tables on Filegroups | phoebix()