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: Brian Cryer 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.