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.