Sunday, April 24, 2011

T-SQL - Find which Object resides in which FileGroup

Select Distinct OBJECT_NAME(SI.object_id) AS ObjectName
,OBJECTPROPERTYEX(SI.object_id,'BaseType') AS ObjectType
,FG.Name AS FileGroupName
from sys.indexes SI , sys.filegroups FG
where SI.data_space_id = FG.data_space_id
Order by ObjectName