[SOLVED] Efficient way to change the table's filegroup

Efficient way to change the table's filegroup

I have around 300 tables which are located in different partition and now these tables are not in use for such huge data as it was. Now, I am getting space issue time to time and some of but valuable space is occupied by the 150 filegroups that was created for these tables so I want to change table's filegroup to any one instead of 150 FG and release the space by deleting these filegroups.

FYI: These tables are not holding any data now but defined many constraints and indices.

Can you please suggest me, how it can be done efficiently ?

Solution

I found few more information on the ways of changing the FG group of existing table:

1- Define clustered index in every object using NEW_FG (Mentioned in @under answer)

CREATE UNIQUE CLUSTERED INDEX <INDEX_NAME> ON dbo.<TABLE_NAME>(<COLUMN_NAME>) ON [FG_NAME]

2- If we can't define clustered index then copy table and data structure to new table, drop old and rename new to old as below

Changes Database's default FG to NEW_FG so that every table can be created using INTO, under that new FG by default

ALTER DATABASE <DATABASE> MODIFY FILEGROUP [FG_NAME] DEFAULT

IF OBJECT_ID('table1') IS NOT NULL 
BEGIN
    SELECT * INTO table1_bkp FROM table1
    DROP TABLE table1
    EXEC sp_rename table1_bkp, table1
END

After all the operation Database's default FG as before

ALTER DATABASE <DATABASE> MODIFY FILEGROUP [PRIMARY] DEFAULT

3- Drop table if feasible then create it again using NEW_FG

DROP TABLE table1 
CREATE TABLE [table1] ( 
    id int,
    name nvarchar(50),
    --------
) ON [NEW_FG]