How can I find which object belongs to which filegroup. Is there any way to know this?
SELECT quotename(schema_name(o.schema_id)) +'.'+ quotename(o.[name]) ObjectName, o.[type], i.[name], i.[index_id], f.[name] FROM sys.indexes i INNER JOIN sys.filegroups f ON i.data_space_id = f.data_space_id INNER JOIN sys.all_objects o ON i.[object_id] = o.[object_id] WHERE i.data_space_id = f.data_space_id AND o.type = 'U' --and f.name='DataFileGroup'