The following script lists link between database objects and filegroups. You can also use this script to identify filegroup with no objects associated
SQL2000
IF OBJECT_ID('tempdb.dbo.#FileGroup') IS NOT NULL
DROP TABLE #FileGroup
IF OBJECT_ID('tempdb.dbo.#ObjectFileGroup') IS NOT NULL
DROP TABLE #ObjectFileGroup
CREATE TABLE #FileGroup (
FileGroup sysname
)
CREATE TABLE #ObjectFileGroup (
ObjectName sysname,
ObjectType varchar(20),
FileGroupID int,
FileGroup sysname
)
SET NOCOUNT ON
DECLARE @TableName sysname
DECLARE @id int
DECLARE cur_Tables CURSOR FAST_FORWARD FOR
SELECT TableName = [name], id FROM dbo.sysobjects WHERE type = 'U'
OPEN cur_Tables
FETCH NEXT FROM cur_Tables INTO @TableName, @id
WHILE @@FETCH_STATUS = 0
BEGIN
TRUNCATE TABLE #FileGroup
INSERT #FileGroup (FileGroup)
EXEC sp_objectfilegroup @id
INSERT #ObjectFileGroup (ObjectName, ObjectType, FileGroupID, FileGroup)
SELECT @TableName, 'TABLE', FILEGROUP_ID(FileGroup), FileGroup
FROM #FileGroup
FETCH NEXT FROM cur_Tables INTO @TableName, @id
END
CLOSE cur_Tables
DEALLOCATE cur_Tables
INSERT #ObjectFileGroup (ObjectName, ObjectType, FileGroupID, FileGroup)
SELECT OBJECT_NAME(id) + ' * ' +[name], 'INDEX', groupid, FILEGROUP_NAME(groupid) FROM dbo.sysindexes
WHERE FILEGROUP_NAME(groupid) IS NOT NULL
AND OBJECT_NAME(id) NOT LIKE 'sys%'
AND [name] NOT LIKE '_WA_Sys%'
AND [name] NOT LIKE 'Statistic_%'
SELECT FileGroupName = FILEGROUP_NAME(sf.groupid),/*ofg.FileGroup, */ofg.ObjectName, ofg.ObjectType, FileName = sf.filename, FileSize = sf.[size] / 128
FROM #ObjectFileGroup ofg
RIGHT JOIN dbo.sysfiles sf
ON ofg.FileGroupID = sf.groupid
ORDER BY FileGroup, ObjectName
SQL 2005 (very simplified)
SELECT
fg.data_space_id, fg.name,
ObjectName = OBJECT_NAME(p.object_id), p.index_id
,df.name, df.physical_name, [Size] = df.size*8/1024
FROM sys.filegroups fg
LEFT JOIN sys.database_files df
ON fg.data_space_id = df.data_space_id
LEFT JOIN sys.partitions p
ON fg.data_space_id = p.partition_number
WHERE (p.object_id>4096 or p.object_id IS NULL)