Determining FileGroup Dependencies

Our database has quite a few (20+) filegroups. i believe several of them are no longer used, but want to verify before I delete them. Is there a query I can run that will tell me what tables and/or indexes are dependent on a specific filegroup?
LVL 15
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
check out if you find the procedure sp_objectfilegroup (in master database).
that code will give you the query you need to join the sysindexes to the sysfilegroups table (note: even a table with no any indexes has an entry in sysindexes)

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
dbbishopAuthor Commented:

select *
from sysfilegroups s, sysobjects i
where i.groupid = s.groupid
and groupname = 'groupname'

Appears to work for indexes in a filegroup, but how about tables?
Guy Hengel [angelIII / a3]Billing EngineerCommented:
I repeat/reformulate:
every table has (at least) an index entry...
if the table has a clustered index (indid=1), that one is the "location" of the table
if the table does not have a clustered index (indid=0), that one is the location of the table

so, you actually check the indexes on the filegroups, if there is no index on a filegroup, there is also no table on the filegroup.
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

dbbishopAuthor Commented:
non-clustered indexes can be in a different filegroup than the table. Thus, if I run the above query (especially if it is a filegroup that is used primarily for indexes) if it does not return anything. This does not appear to be foolproof.
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>non-clustered indexes can be in a different filegroup than the table.
yes, but there will still be a internal index on the table anyhow, even if there is no clustered index on the table, or other indexes on it.

check out the below query, it will return the filegroup with the object type and the counts for that tuplet for the current database.
reading the query:
indid = 0 -> table with no clustered index
indid = 1 -> table with clustered index
indid > 1 -> non-clustered index

select s.groupname
, case i.[indid] when 0 then 'table with no clustered index' when 1 then 'table with clustered index' else 'normal index' end object_type
, count(*)  c
from sysfilegroups s
join sysindexes i
on i.groupid = s.groupid
group by s.groupname
, case i.[indid] when 0 then 'table with no clustered index' when 1 then 'table with clustered index' else 'normal index' end 

Open in new window

dbbishopAuthor Commented:
Thanks. That did it.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.