-- start with this:
SELECT
dbfile.name AS DatabaseFileName,
dbfile.size/128 AS FileSizeInMB,
sysFG.name AS FileGroupName,
dbfile.physical_name AS DatabaseFilePath
FROM
sys.database_files AS dbfile
INNER JOIN
sys.filegroups AS sysFG
ON
dbfile.data_space_id = sysFG.data_space_id
-- for a more general look by filegroup, try this:
with fileConfig as
(SELECT
dbfile.name AS DatabaseFileName,
(dbfile.size/128) AS FileSizeInMB,
sysFG.name AS FileGroupName,
dbfile.physical_name AS DatabaseFilePath
FROM
sys.database_files AS dbfile
INNER JOIN
sys.filegroups AS sysFG
ON
dbfile.data_space_id = sysFG.data_space_id
)
select FileGroupName,
sum(FileSizeInMB) as TotalFilegroupInMB
from fileConfig
group by FileGroupName
order by FileGroupName