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
Microsoft SQL Server 2008Microsoft SQL Server

Avatar of undefined
Last Comment
TimHudspith

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
Habib Pourfard

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
ASKER
TimHudspith

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.
ASKER
TimHudspith

Actually, disregard what I said. It works.
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck