November 4, 2003 at 12:24 pm
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
LMT
November 4, 2003 at 12:42 pm
do you mean something like
SELECT
sysFile.groupid AS GroupID, SUBSTRING(sysFile.groupname,1,30) AS FilegroupName,
SUBSTRING(sysObj.name,1,30) AS ObjectName
FROM sysobjects sysObj
INNER JOIN sysindexes sysIdx
ON sysObj.id = sysIdx.id
INNER JOIN sysfilegroups sysFile
ON sysIdx.groupid = sysFile.groupid
WHERE
sysIdx.indid = 0 and sysObj.xtype = 'U'
ORDER BY
sysFile.groupname, sysObj.ObjectName
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
November 4, 2003 at 12:52 pm
Thanks a million.
I did now to use "indid"...that answered my question
You have no idea how much headache solves me!
November 5, 2003 at 8:50 am
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
November 5, 2003 at 9:11 am
The table will spread on all files that make up the filegroup.
November 5, 2003 at 10:30 am
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
repectfully
lmt
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply