tcronin 95651 (7/16/2014)
Inherited large database with over 26 data files. Looking for a query to show what objects are on which files. I have one which list all the indexes are on which files, trying to found out what objects are on all the other files.
As already pointed out, objects (tables\indexes) are created across filegroups, not individual files.
This query will identify the table and its type
USE [yourdb]
select object_name(i.object_id),
CASE
WHEN i.name IS NULL THEN 'HEAP'
ELSE i.name
END
f.name
from sys.indexes i
inner join sys.objects o
on i.object_id = o.object_id
inner join sys.filegroups f
on i.data_space_id = f.data_space_id
where o.is_ms_shipped <> 1
order by object_name(i.object_id)
tcronin 95651 (7/16/2014)
PS looking for objects on the logical file groups. I have a file group name called NIndex_1 but it has 18 logical files. Looking for what is on the logical files.
see above for object filegroup placement
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉