How to find what Tables and Indexes are on what filegroup

Hello,
Is there a query that can be written to determine what Tables and indexes
are on a particular file group?
Thanks
Anubis.sp_help 'YourTable' will return the filegroup in one of the
resultsets that is returned. There are also some
undocumented ways such as:
sp_objectfilegroup @.objid
or querying the system tables...something like:
SELECT so.name, sfg.groupname
FROM sysobjects so
INNER JOIN sysindexes si
ON so.id=si.id
INNER JOIN sysfilegroups sfg
ON si.groupid=sfg.groupid
WHERE si.indid < 2
AND so.type = 'U'
-Sue
On Tue, 14 Jun 2005 12:17:47 +1000, "Anubis"
<anubis@.iwwd.com> wrote:

>Hello,
>Is there a query that can be written to determine what Tables and indexes
>are on a particular file group?
>Thanks
>Anubis.
>|||Depends on what info you want to find out. But it will be some
variation on this:
select object_name(i.[id]) as tablename, i.*
from dbo.sysindexes as i
inner join dbo.sysfilegroups as g on g.groupid = i.groupid
where g.groupname = 'PRIMARY'
If it's just names you're after then the column list in the select
statement will be something like "select object_name(i.[id]) as
tablename, i.[name] as indexname ..." but there's heaps of other info
you can find out from dbo.sysindexes, like the index type (clustered,
nonclustered, heap, LOB data - all from the indid), etc.
*mike hodgson* |/ database administrator/ | mallesons stephen jaques
*T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
*E* mailto:mike.hodgson@.mallesons.nospam.com |* W* http://www.mallesons.com
Anubis wrote:

>Hello,
>Is there a query that can be written to determine what Tables and indexes
>are on a particular file group?
>Thanks
>Anubis.
>
>|||I wrote a script that does exactly what you ask (and more)
http://education.sqlfarms.com/ShowPost.aspx?PostID=48
for your convenient download. Enjoy.
The script lists all the tables and indexes filgroup, as well as provides
other information.
Omri Bahat
SQL Farms Solutions
www.sqlfarms.com