List All Objects Created on All Filegroups in a Database

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'

 

image_thumb

Leave a Reply

Your email address will not be published. Required fields are marked *