I have a table in SQL Server 2008 R2. I want to know what file the data is being stored on. I know what filegroup the table is stored on. But I want to know which file it's on.
Or is that not possible? I guess the whole idea of filegroups is to have one table/index span multiple physical drives, so maybe the table is split between the two files in the filegroup.
Is that right?
For all intents and purposes, you should assume that your table's data is spread across all of the files in its filegroup. You can probably manually inspect all of the pages using undocumented stuff like DBCC PAGE, but I fail to see the point. If you find even one page in each file, what are you going to do then? If you go through and eliminate one of the files as not having any rows, by the time you do anything about it, it might. The whole point of pointing a table or index or partition at a multi-file filegroup is to spread the data across those files, not create a hot spot in any single file.