We help IT Professionals succeed at work.

Determining FileGroup Dependencies

Doug Bishop
Doug Bishop asked
on
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?
Comment
Watch Question

Billing Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009
Commented:
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)
Doug BishopDatabase Developer

Author

Commented:
angelIII:

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 Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009

Commented:
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.
Doug BishopDatabase Developer

Author

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 Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009
Commented:
>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

Doug BishopDatabase Developer

Author

Commented:
Thanks. That did it.

Explore More ContentExplore courses, solutions, and other research materials related to this topic.