Link to home
Start Free TrialLog in
Avatar of TimHudspith
TimHudspith

asked on

Which tables belong to which filegroup in SQL Server 2008

I use the following code to list tables and filegroups. This lists both user and system tables but I only want to list the user tables (which are prefixed 'T_').


select tablename = object_name(object_id),Data_located_on_filegroup = d.name  
from sys.data_spaces d
join sys.indexes i on d.data_space_id = i.data_space_id
where i.index_id < 2
ORDER by tablename DESC
ASKER CERTIFIED SOLUTION
Avatar of Habib Pourfard
Habib Pourfard
Flag of New Zealand image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of TimHudspith
TimHudspith

ASKER

That only returned one record, but in fact I have six tables, so I looked at the two tables that your code refers to. In sys.data_spaces I have 3 filegroups: primary (data_space_id = 1), filestream (2), secondary (3). However, with one exception, my user tables in sys.tables have a data_space_id value of 0, which of course doesn't represent any of the actual filegroups.

I'm new to SQL server and have done a bit of experimenting with filegroups and tables, also I didn't have SP3 installed when doing this. I remember getting some odd messages when trying to refresh/save objects so I expect it's connected to that.

What I'm aiming at ultimately is moving my user tables to the secondary filegroup.
Actually, disregard what I said. It works.