May 14, 2008 at 3:39 pm
I need a query which will return the names of all of the tables on the filegroup of a given name.
May 15, 2008 at 6:50 am
More...
I'm hoping to use something like;
SELECT [TABLENAME] from
WHERE FileGroup = [FILEGROUPNAME]
Am I dreaming in technicolor?
May 15, 2008 at 7:00 am
Somebody verify this, but I think you can use the query below to accomplish that. It gives the the filegroup that the clustered index is on, which is where all the actual data is. But you could keep in mind you could have unclustered indexes on a different filegroup than the clustered index.
select
tables.name as table_name,
groups.name as file_group
from
sys.indexes indexes
inner join sys.tables tables
on indexes.object_id = tables.object_id
inner join sys.filegroups groups
on indexes.data_space_id = groups.data_space_id
where
indexes.type = 1 -- clustered
The Redneck DBA
May 15, 2008 at 7:46 am
Hi Jason;
Thanks for the post. I had some trouble with the syntax. I don't have sys.tables or sys.indexes. I think sys.indexes should have been dbo.sysindexes
Anyway - this query seems to give me what I'm looking for;
SELECT DISTINCT
OB.Name
,FG.GroupName
FROM dbo.sysobjects OB
JOIN dbo.sysindexes IND
ON IND.ID = OB.ID
JOIN dbo.sysfilegroups FG
ON FG.GroupId = IND.GroupId
WHERE
FG.GroupName = 'Data'
ORDER BY
OB.Name
May 15, 2008 at 7:48 am
I'm sorry! I sent you SQL 2005 code.
The Redneck DBA
May 15, 2008 at 8:10 am
That would explain it.
We're in the midst of transitioning from 2000 to 2005. I'm kind of schizophrenic myself these days 😉
May 15, 2008 at 9:05 am
In sysindexes there's a GROUPID, which is hte filegroup ID, you can join that to sysfilegroups to get the name of the filegroup. Not sure you can get to the files.
This was a fairly immature feature in SQL Server 2000.
May 15, 2008 at 10:43 pm
Just don't forget - non-clustered indexes may reside in different filegroups then table itself.
_____________
Code for TallyGenerator
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply