January 3, 2008 at 8:59 am
I have several databases that have nothing but the standard primary filegroup and tables keep getting added to this Db. I inherited this and yes I know it is bad deisgn. So, I move the tables to different filegroups via clustering indexes and a while-loop.
I moved most tables but having to look at the properties 1-by-1 is painful to even think about. How can I get the tablenames and respective filegroup in one shot?
Thanks a bunch!
January 3, 2008 at 9:03 am
Just stumbled upon DBCC CHECKFILEGROUP
gives too much information
January 3, 2008 at 9:45 am
It's not necessarily bad to have things in the primary filegroup. I know some people prefer to have the system data in a separate filegroup, but especially for small databases, I don't think it's a big deal.
You can mark one of the new filegroups as default and all new objects will get put there.
January 3, 2008 at 9:49 am
guess I was clearer in my head than on paper. I like to query say 'sysobjects' or some system table and get a list displaying tablename and associated filegroup.
January 3, 2008 at 11:37 am
Take a look at the catalog views. Sys.Tables & Sys.Data_Spaces. This is quick & dirty:
select t.name as TableName
,s.name as FileGroupName
from sys.tables t
join sys.data_spaces s
on s.data_space_id = t.lob_data_space_id
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply