Sometimes managing multiple file groups and the amount of data that is in them can cause a few logistic issues. Recently I found myself needing to know what tables are in what file groups and what the sizes of those tables are. With the database the size it is and the number of objects that exist in it, I needed a script. The goal of this script is only to define the tables, and the sizes of the tables/indexes and what file group the tables are on (not the location of indexes). Consider it a quick reference. I have not tested it against partitioned tables yet however I plan to in the future.
To get the below script I started a profiler trace, and then executed the standard “Disk Usage By Table” report. After I found the query that was executed by the report I copied it and then added some basic file group data collecting to it.
SELECT
--(row_number() over(order by a3.name, a2.name))%2 as l1,
a3.name AS [schemaname],
a2.name AS [tablename],
a5.FileGroup as [filegroup],
a1.rows as row_count,
(a1.reserved + ISNULL(a4.reserved,0))* 8 /1024 AS 'reserved (mb)',
a1.data * 8 /1024 AS 'data (mb)',
(CASE WHEN (a1.used + ISNULL(a4.used,0)) > a1.data THEN (a1.used + ISNULL(a4.used,0)) - a1.data ELSE 0 END) * 8 /1024 AS 'index_size (mb)',
(CASE WHEN (a1.reserved + ISNULL(a4.reserved,0)) > a1.used THEN (a1.reserved + ISNULL(a4.reserved,0)) - a1.used ELSE 0 END) * 8 / 1024 AS 'unused (mb)'
FROM
(SELECT
ps.object_id,
SUM (
CASE
WHEN (ps.index_id < 2) THEN row_count
ELSE 0
END
) AS [rows],
SUM (ps.reserved_page_count) AS reserved,
SUM (
CASE
WHEN (ps.index_id < 2) THEN (ps.in_row_data_page_count + ps.lob_used_page_count + ps.row_overflow_used_page_count)
ELSE (ps.lob_used_page_count + ps.row_overflow_used_page_count)
END
) AS data,
SUM (ps.used_page_count) AS used
FROM sys.dm_db_partition_stats ps
GROUP BY ps.object_id) AS a1
LEFT OUTER JOIN
(SELECT
it.parent_id,
SUM(ps.reserved_page_count) AS reserved,
SUM(ps.used_page_count) AS used
FROM sys.dm_db_partition_stats ps
INNER JOIN sys.internal_tables it ON (it.object_id = ps.object_id)
WHERE it.internal_type IN (202,204)
GROUP BY it.parent_id) AS a4 ON (a4.parent_id = a1.object_id)
INNER JOIN sys.all_objects a2 ON ( a1.object_id = a2.object_id)
INNER JOIN sys.schemas a3 ON (a2.schema_id = a3.schema_id)
Join
(
Select groupid, data_space_id, indx.id, ds.name as 'FileGroup', ob.name as 'tblName'
from sysindexes indx
join sys.data_spaces ds ON indx.groupid = ds.data_space_id
join sysobjects ob ON [ob].[id] = [indx].[id]
where indx.indid = 1 or indx.indid = 0
and ob.type = 'u'
) AS a5 on a2.name = a5.tblName
WHERE a2.type <> N'S' and a2.type <> N'IT'
ORDER BY a3.name, a2.name
Chris Shaw's Weblog 
Chris, This query uses some outdated compatibility views in the last join which filters out some tables from the final result set. If you replace that last section with this it should be good to go for later versions of SQL Server.
Join
(
SELECT indx.data_space_id, indx.object_id, ds.name AS ‘FileGroup’, ob.name AS ‘tblName’, ob.type
FROM sys.indexes AS indx
INNER JOIN sys.data_spaces AS ds ON indx.data_space_id = ds.data_space_id
INNER JOIN sys.objects AS ob ON [ob].[object_id] = [indx].[object_id]
WHERE (indx.index_id IN (1,0)) and ob.[type] = ‘U’
) AS a5 on a2.name = a5.tblName
Andy Kelly
Thanks Andy for the comments. I am going to take a deeper look at this and adjust where needed.