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 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.
Strate SQL 
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.
LikeLike
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)
LikeLike
Ah definitely! I’ll have to update my script. Thanks, Merrill.
LikeLike
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;
LikeLike
Thanks, Stefan. I hadn’t remembered to consider partitions when I wrote this the other day. I’ve updated the post to include this.
LikeLike