One filegroup, multiple datafiles, how to get list of tables in each file In one of my production databases, I have a filegroup with 3 data files sitting on 3 different disks. There are over 50 tables on this filegroup.

How do I identify the location of each of the 50 tables between the 3 data files? I need to know on which data file does the clustered index for each table reside.

Example : 

Table A : Datafile 1 : FG1
Table B : Datafile 2 : FG1
Table C : Datafile 1 : FG1
Table D : Datafile 3 : FG1
An object in a filegroup will use all datafiles in the filegroup. Any table in FG1 resides equally on Datafile1, Datafile2 and Datafile3. If you need to control placement you need to create distinct filegroups. To remove a file from a file group use DBCC SHRINKFILE EMPTYFILE.