Show tables which use a data file?

  • Is there a way to show which tables are using a given data file? I know how to find which filegroup a table is in but I would like to go one step further by finding the data file(s) the table uses.


  • Pages are allocated in a round-robin fashion among all the files in a filegroup.  If the table is more than a few extents, it probably has pieces in every file.

    If the fate of humanity depended on you finding out exactly where the table is stored, you could go to sysindexes and find the FirstIAM value for the table from the row with indid = 0 or 1, then figuring out how to use DBCC PAGE to dump the pages in the IAM chain.  Have fun.

  • Thanks Scott. I didn't think there was an easy way to find this.

  • this might help: it shows each table, and what filegroup a table belongs to. if you only have ONE datafile per filegroup, then the table is contained in that datafile.

    if you have mulitple datafiles per file group, then as Scott Identified, the data is placed in a round robin fashion, so you it's distributed across all datafiles for the filegroup.

    I think there is an information schema that basically ahs the same data, but here you go:

    SELECT OBJECT_NAME( i."id" ) AS TableName ,

           i."Name" AS IndexName ,

           FILEGROUP_NAME( i.groupid ) AS FileGroupName

    FROM sysindexes AS i

    WHERE ( i.indid IN ( 0 , 1 ) Or i.indid < 255 ) And -- Tables & indexes only

          OBJECTPROPERTY( i."id" , 'IsUserTable' ) = 1 And -- User tables only

          OBJECTPROPERTY( i."id" , 'IsMSShipped' ) = 0 And -- No system tables

          COALESCE( INDEXPROPERTY( i."id" , i."Name" , 'IsStatistics' ) , 0 ) = 0 And -- No Statistics / Auto-Create stats

          COALESCE( INDEXPROPERTY( i."id" , i."Name" , 'IsHypothetical' ) , 0 ) = 0   -- No Hypothetical statistics

    ORDER BY FileGroupName , TableName , IndexName


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply