Lists all filegroups in the current database, number of files, number of objects, total size of the files, total space used, total free space, and percentage of free space

FilegroupSummary.sql

WITH fg_sizes AS (
SELECT fg.data_space_id AS FGID, 
	COUNT(f.file_id) AS FileCount,
	ROUND(CAST(SUM(f.size) AS FLOAT)/128,2) AS Reserved_MB,
	ROUND(CAST(SUM(FILEPROPERTY(f.name,'SpaceUsed')) AS FLOAT)/128,2) AS Used_MB,
	ROUND((CAST(SUM(f.size) AS FLOAT)/128)-(CAST(SUM(FILEPROPERTY(f.name,'SpaceUsed'))AS FLOAT)/128),2) AS Free_MB
FROM sys.filegroups fg
LEFT JOIN sys.database_files f ON f.data_space_id = fg.data_space_id
GROUP BY fg.data_space_id
),
fg_objs AS (
	SELECT ID AS FGID, SUM(n) AS TotalObjects
	FROM (
		SELECT au.data_space_id AS ID, 
			COUNT(*) AS n
		FROM sys.allocation_units au
		WHERE au.[type] = 1
		GROUP BY au.data_space_id
		UNION ALL
		SELECT t.lob_data_space_id AS ID,
			COUNT(*) AS n
		FROM sys.allocation_units au
			JOIN sys.partitions p ON au.container_id = p.partition_id
			JOIN sys.objects o ON p.object_id = o.object_id
			LEFT JOIN sys.tables t ON o.object_id = t.object_id
		WHERE au.[type] = 1
			AND au.data_space_id <> t.lob_data_space_id
		GROUP BY t.lob_data_space_id
	) q
	GROUP BY ID
)
SELECT fg.[data_space_id] AS FilegroupID, 
	fg.[name] AS FilegroupName,
	fgs.FileCount,
	ISNULL(fgo.TotalObjects,0) AS ObjectCount,
	CONVERT(VARCHAR,CONVERT(MONEY,MAX(fgs.Reserved_MB)),1) AS Reserved_MB,
	CONVERT(VARCHAR,CONVERT(MONEY,MAX(fgs.Used_MB)),1) AS Used_MB,
	CONVERT(VARCHAR,CONVERT(MONEY,MAX(fgs.Free_MB)),1) AS Free_MB,
	ROUND(MAX(fgs.Free_MB)/MAX(fgs.Reserved_MB)*100,2) AS Percent_Free
FROM sys.filegroups fg
INNER JOIN fg_sizes fgs ON fg.data_space_id = fgs.FGID
LEFT JOIN fg_objs fgo ON fg.data_space_id = fgo.FGID
GROUP BY fg.data_space_id, fg.name, fgs.FileCount, fgo.TotalObjects
ORDER BY Percent_Free DESC;