Find tables in Filegroup 1

[TalentedFool]

How can I find out which filegroup a table has been created in ?

For example if I use

create table test1(
Field1 varchar(1)
)
on SomeFileGroup

Then I know that the test1 table will be stored in the SomeFileGroup filegroup.

I've searched all the built in tables such as sysfilegroups, sysobjects etc to find a link between them but just can't see one.

Anybody help?

Just looking to tidy up my database and order things.

Cheers

~ Remember - Nothing is Fool Proof to a Talented Fool ~

 

[JamesLean]

Each table will have an entry in sysindexes with an indid of either 0 (heap) or 1 (clustered index). You can then link to sysfilegroups using the groupid.

--James

 

[JamesLean]

Something like this:

SELECT o.name, g.groupname
FROM sysobjects o
	JOIN sysindexes i ON o.id = i.id
		AND i.indid IN (0, 1)
	JOIN sysfilegroups g ON i.groupid = g.groupid
ORDER BY o.name

--James

 

[TalentedFool]

Thanks James - gold star - well Purple one anyway ;-)

Much appreciated

~ Remember - Nothing is Fool Proof to a Talented Fool ~