[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 463
  • Last Modified:

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?
0
dbbishop
Asked:
dbbishop
  • 3
  • 3
2 Solutions
 
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)
0
 
dbbishopAuthor 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?
0
 
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.
0
[Webinar] Improve your customer journey

A positive customer journey is important in attracting and retaining business. To improve this experience, you can use Google Maps APIs to increase checkout conversions, boost user engagement, and optimize order fulfillment. Learn how in this webinar presented by Dito.

 
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.
0
 
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

0
 
dbbishopAuthor Commented:
Thanks. That did it.
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now