tables location on data files

  • hi all,

    is there a possibility to know what tables are located in which datafiles?

    considering that in the sql2000 systables schema there are 1-many relationship between sysfiles and sysfilegroups and sysindexes and sysfilegroups, I do not know how to determine in which file /by name I have which tables.

    Example: dw_test.MDF (PRIMARY filegroup) and dw_test_1.MDF(PRIMARY filegroup ) contain X tables...but which table in dw_test.MDF and which in dw_test_1.MDF.

    Thank you very much


  • do you mean something like

    sysFile.groupid AS GroupID, SUBSTRING(sysFile.groupname,1,30) AS FilegroupName,

    SUBSTRING(,1,30) AS ObjectName

    FROM sysobjects sysObj

    INNER JOIN sysindexes sysIdx
    ON =

    INNER JOIN sysfilegroups sysFile
    ON sysIdx.groupid = sysFile.groupid

    sysIdx.indid = 0 and sysObj.xtype = 'U'

    sysFile.groupname, sysObj.ObjectName


    Frank Kalis
    Microsoft SQL Server MVP
    My blog:[/url]

  • Thanks a million.

    I did now to use "indid"...that answered my question

    You have no idea how much headache solves me!

  • Maybe I am missing something here. The query only states the filegroup, not the file in the file group.

    I spent some time a year ago looking for the file the table was in. The first IAM may help show where the first page of the table is, but I am not sure you could even gaurantee that the table was in just one file since the files all make up the filegroup.

    "Keep Your Stick On the Ice" ..Red Green

  • The table will spread on all files that make up the filegroup.

  • jeffwe you are right.But I think that is the closest.

    What I did is to create datafiles and move my tables - wrote a note with where the tables are and so on.

    Interesting that I had to kill the statistics - they remain in the old data file.So I killed them and the recreation was in the new data file.

    as a note =>>I think SQL should do more about the file managemment.Yukon presents a series of tools but is only make-up.I did not see anything specific - file management, better control of tablespaces, better file backup etc.

    If anybody socceede to really see specifically into the data file I will be happy to know more about this



Viewing 6 posts - 1 through 5 (of 5 total)

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