I am looking to eliminate an extraneous data file in a filegroup in my DB but don't want to use the dreaded DBCC SHRINKFILE method, I am preferring to rebuild indexes into an alternate FG, perform the shrink with EMPTYFILE followed with removal of the file and then rebuild back into the original filegroup. Is there a method to identify which table/index objects are populating the utilized pages/extents in the target database file?
You should assume, for all intents and purposes, that parts of every object in a given filegroup will be distributed across all of the files in that filegroup. You might be able to work up some script that will prove one or two smaller tables don't have any data on file 5 or file 8, but for what purpose? I say it's easier to just write a script that will rebuild all of the tables in that filegroup.
DBCC IND can get you that information.
DBCC IND (‘databasename’, ‘tablename′, 1);
The third parameter is the index_id for the index that you want to look at.
DBCC IND returns one row per page used by the index. The first column "PageFID" is the file id that that page lives in.
Besides that, I agree with Aaron. Use
DBCC SHRINKFILE to empty the file. Afterwards rebuild the indexes affected.