How to list objects in a filegroup
-
I have a DB that has 20 filegroups and would like to consolidate them and keep the number low. Is there a way for me to list all the objects in a particular filegroup?
I've tried sysobjects table but it doesn't seem to show the filegroups each object belongs to.
Thanks.
-
Something like this may work for you (you will probably want to tweek it to your needs)
select
groupname,d.name
from
sysfiles a
join
sysfilegroups b on a.groupid = b.groupid
join
sysindexes c on b.groupid = c.groupid
join
sysobjects d on c.id = d.id
where
indid=0
Francis
-----------------
SQLRanger.com -
Thanks, Fapel. It works like a charm.
-
just to be complete :
where indid=0 gives only heaps !
where indid in (0 , 1) gives heaps and clustered indexes
Without this where clause you'll get all objects in the filegroup(s)
Here's a demoscript
create table T_ALZDBAxxx (col1 int not null, col2 int not null)
go
select groupname,d.name as PrimaryObject, c.name as IndexName
from sysfiles a
join sysfilegroups b on a.groupid = b.groupid
join sysindexes c on b.groupid = c.groupid
join sysobjects d on c.id = d.id
where d.name like '%ALZDBAxxx%'
and indid = 0
create clustered index x_ALZDBAxxx on t_ALZDBAxxx (col1)
go
select groupname,d.name as PrimaryObject, c.name as IndexName
from sysfiles a
join sysfilegroups b on a.groupid = b.groupid
join sysindexes c on b.groupid = c.groupid
join sysobjects d on c.id = d.id
where d.name like '%ALZDBAxxx%'
and indid = 0
go
select groupname,d.name as PrimaryObject, c.name as IndexName
from sysfiles a
join sysfilegroups b on a.groupid = b.groupid
join sysindexes c on b.groupid = c.groupid
join sysobjects d on c.id = d.id
where d.name like '%ALZDBAxxx%'
and indid in (0,1)
go
create index Y_ALZDBAxxx on t_ALZDBAxxx (col2)
go
select groupname,d.name as PrimaryObject, c.name as IndexName
from sysfiles a
join sysfilegroups b on a.groupid = b.groupid
join sysindexes c on b.groupid = c.groupid
join sysobjects d on c.id = d.id
where d.name like '%ALZDBAxxx%'
and indid in (0,1)
go
select groupname,d.name as PrimaryObject, c.name as IndexName
from sysfiles a
join sysfilegroups b on a.groupid = b.groupid
join sysindexes c on b.groupid = c.groupid
join sysobjects d on c.id = d.id
where d.name like '%ALZDBAxxx%'
go
-- cleanup when done
-- drop table T_ALZDBAxxx
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
Thanks, Alzdba, for the additional insight.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply