February 6, 2007 at 5:06 pm
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.
February 7, 2007 at 7:17 am
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.
February 7, 2007 at 9:30 am
Thanks Scott. I didn't think there was an easy way to find this.
February 7, 2007 at 11:05 am
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
Lowell
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply