October 15, 2003 at 3:48 am
I have 3 different filegroups within my database and I want to be able to identify all of the tables located within one of the filegroups.
Does anyone know where this nugget of info is stored within SQL2K? I thought it might be on the SYSOBJECTS table but it's not clear to me whether it is or not.
I want to dump out to disk the contents of the tables on one filegroup which contain data entered by users from the front end. I do not have 100% confidence in our backup procedures and if it goes pear shaped then I have an alternative source of data for the restore.
The other file groups contain data created through batch processing which I can recreate by rerunning the batch jobs again.
Thanks
Jeremy
October 15, 2003 at 4:33 am
This information can be found in the sysfilegroups table via sysindexes (for the clustered or heap index - id 1 or 0)
If you look at the system stored proc sp_objectfilegroup that should give you the joins you require.
October 15, 2003 at 5:17 am
Thanks. I thought it must be simple but wasn't sure where to look.
One further question. What answer does objectfilegroup give when the index and the table are on different filegroups (for performance reasons)? If I have a clustered index on the table I think it will return the filegroup for the index (indid = 1) rather than filegroup for the heap (indid = 0).
All of my indexes are in the same filegroup as the table as I only have a single drive but I am curious.
Thanks.
Jeremy
October 15, 2003 at 5:26 am
Sp_objectfilegroup doesn't work for indexes (look at sp_helpindex instead).
However, the basic mapping from sysindexes to sysfilegroups (on column groupid) is exactly the same for the base table and the indexes.
October 16, 2003 at 3:11 am
This might help a little although its a first stab and needs a bit of attention !
select sysindexes.name as 'Object Name',
sysobjects.name as 'Related Table Name',
sysfilegroups.groupname as 'Filegroup'
from sysindexes
join sysfilegroups
on sysindexes.groupid = sysfilegroups.groupid
join sysobjects
on sysobjects.id = sysindexes.id
where sysobjects.type = 'U'
order by sysfilegroups.groupname, sysobjects.name
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply