SQL Server Tables + files

[sendba]

Hi,

Is there a way to figure out what is being allocated in the data files. for eg I have 10 files on the primary filegroup and wanted to shrink it to 1 in order for me to make different filegroups. The database is huge. Is there any way to determine what is being allocated in a file.

Thanks
Sen

 

[MartinCroft]

This is something I have been after for a while, saw your post and decided to work it out. managed to get there by seeing how SQL managed it, sp_help objectname gave file info which in turn called sp_objectfilegroup which led me to the code I amended below.

SELECT 
	o.name AS ObjectName,
	s.groupname  AS Data_located_on_filegroup 
FROM 
	sysfilegroups s
JOIN
	sysindexes i  ON i.groupid = s.groupid  
JOIN
	sysobjects o  ON o.id=i.id
WHERE 
	i.indid < 2  AND o.type ='U'
ORDER BY 1 
	Desc