Brian Cryer

 

Cry How to...

List all tables or indexes within a filegroup


To list all the tables in a filegroup use:

select distinct(object_name(id)) from sysindexes
where groupid=filegroup_id('<filegroup-name>')

To list all indexes in a filegroup use:

select name from sysindexes
where groupid=filegroup_id('<filegroup-name>')
and indid > 0

where:

<filegroup-name>

is the name of the filegroup for which the list of tables or indexes is required.

for example, to list all the tables in a file group called "ConfigurationData":

select distinct(object_name(id)) from sysindexes
where groupid=filegroup_id('ConfigurationData')

Note:

  • Be sure to first select the database that contains the file group.
  • If nothing is listed then it may be because the file group that has been specified does not exist - check spelling and which database is currently connected.

These notes have been tested against SQL Server 7.



About the author: is a dedicated software developer and webmaster. For his day job he develops websites and desktop applications as well as providing IT services. He moonlights as a technical author and consultant.