Database Data and Storage By File Groups

;WITH DataSpaces AS (
SELECT	TOP 100 PERCENT
		 T.name AS TableName
		,I.index_id AS IndexId
		,I.name AS IndexName
		,DS.data_space_id
		,DS.name AS ParentDataSpace
		,X.DestDataSpaceId
		,X.DestDataSpace
		,X.FileGroupName
		,X.FileGroupLocation
		,A.used_pages*8. AS UsedPages
	--	,A.used_pages*8./1024.        AS UsedMB
	--	,A.used_pages*8./1024./1024.  AS UsedGB
		,LEFT(FileGroupLocation, CHARINDEX(FileGroupName+'.', X.FileGroupLocation)-1) AS FolderLocation
FROM	sys.tables T
		INNER JOIN sys.schemas S ON S.schema_id = T.schema_id
		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 JOIN sys.partition_schemes PS ON I.data_space_id = PS.data_space_id
		LEFT JOIN sys.partition_range_values RV ON PS.function_id = RV.function_id
			AND P.partition_number = RV.boundary_id+1
		LEFT JOIN sys.data_spaces DS ON I.data_space_id = DS.data_space_id
		LEFT JOIN sys.database_files DF ON DF.data_space_id = DS.data_space_id

		LEFT JOIN sys.destination_data_spaces DDS ON PS.data_space_id = DDS.partition_scheme_id
			AND P.partition_number = DDS.destination_id
		LEFT JOIN sys.data_spaces DDSDS ON DDS.data_space_id = DDSDS.data_space_id
		LEFT JOIN sys.database_files DDSDF ON DDSDF.data_space_id = DDS.data_space_id

		INNER JOIN sys.allocation_units A ON P.partition_id = A.container_id
		OUTER APPLY (
			SELECT	COALESCE(DDSDS.data_space_id, DS.data_space_id) AS DestDataSpaceId
					,COALESCE(DDSDS.name, DS.name) AS DestDataSpace
					,COALESCE(DF.name, DDSDF.name) AS FileGroupName
					,COALESCE(DF.physical_name, DDSDF.physical_name) AS FileGroupLocation
		) X
WHERE	T.NAME NOT LIKE 'dt%'    -- filter out system tables for diagramming
	--	AND T.name LIKE 'tbVtmsHistory'
ORDER BY TableName, IndexId, IndexName, FileGroupName
)
SELECT	FileGroupLocation
		,FileGroupName
		,CONVERT(DECIMAL(10,4), SUM(UsedPages/1024.)) AS UsedMB
		,CONVERT(DECIMAL(10,4), SUM(UsedPages/1024./1024.)) AS UsedGB
FROM	DataSpaces DS
GROUP BY FileGroupLocation
		,FileGroupName
HAVING SUM(UsedPages) > 0
ORDER BY FileGroupLocation, FileGroupName