For a given filegroup, list out object & index info and data/LOB filegroup names

FilegroupDetail.sql

DECLARE @FGID INT;
SET @FGID = ; -- Filegroup ID

SELECT QUOTENAME(s.name) AS SchemaName, 
	QUOTENAME(o.name) AS ObjName,
	o.object_id AS ObjID,
	p.index_id AS IndexID, 
	QUOTENAME(i.name) AS IndexName,
	ROUND(CAST(au.data_pages AS FLOAT)/128,2) AS MB_Used,
	p.data_compression_desc,
	QUOTENAME(f.name) AS DataFilegroup,
	QUOTENAME(f2.name) AS LOBFilegroup
FROM sys.allocation_units au
    INNER JOIN sys.partitions p ON au.container_id = p.partition_id
    INNER JOIN sys.objects o ON p.object_id = o.object_id
    INNER JOIN sys.indexes i ON p.index_id = i.index_id AND i.object_id = p.object_id
    INNER JOIN sys.schemas s ON o.schema_id = s.schema_id
    LEFT JOIN sys.tables t ON o.object_id = t.object_id
    LEFT JOIN sys.filegroups f ON au.data_space_id = f.data_space_id
    LEFT JOIN sys.filegroups f2 ON t.lob_data_space_id = f2.data_space_id
WHERE au.[type] = 1
	AND (au.data_space_id = @FGID OR t.lob_data_space_id = @FGID)
ORDER BY SchemaName, ObjName, p.index_id;