What Tables Are In My Filegroups

One issue I've found a bit troublesome is trying to find out what's in a specific Filegroup. Let's say you're trying to clear off a lun or just a drive and you see a file labeled, "iudexes7.ndf". Now unless you built this and have a steal trap for a memory... or just are fantastic at documentation... you probably have no clue what's in this file. If you target the offending database, you can either run the query below as is and gather this data, or add the where clause and target just that file.

Below is a good script for exploring and cleanup.

select sch.name AS SchemaName,tbl.name AS TableName,idx.name AS IndexName,ds.name AS Filegroup,
data_compression_desc,total_pages,total_pages*8/1024 AS SizeInMB,max_column_id_used,fill_factor
from sys.partitions p
inner join sys.allocation_units au  on au.container_id = p.hobt_id
inner join sys.filegroups fg  on fg.data_space_id = au.data_space_id
inner join sys.tables tbl on tbl.object_id = p.object_id
inner join sys.indexes idx on idx.object_id = p.object_id
inner join sys.schemas sch on sch.schema_id = tbl.schema_id
inner join sys.data_spaces ds on ds.data_space_id = au.data_space_id
and idx.index_id = p.index_id
--where ds.name = 'primary'
order by ds.name, idx.name

This is a good way to move files off a specific drive, clean up wasted space or even just help with some space issues related to a specific file. Happy hunting!