Spostare una tabella da un FileGroup ad un altro

Mai avuto la necessità di spostare una tabella da un filegroup ad un altro ?
Mi è giusto capitato pochi giorni fa e
per risolvere il problema mi sono creato una Stored Procedure che esegue :

1. Salvataggio metadati di tutte le Fk della tabella
2. Drop di tutte le Fk dalla tabella
3. Disable di tutti gli indici
4. Salvataggio metadati indice clustered
5. Drop Indice clustered dalla tabella
6. Create indice clustered con i metadati salvati precedentemente sul nuovo filegroup
7. Rebuild Indici non-clustered
8. Create FK con i metadati salvati precedentemente

Ecco lo script :

--N.B. MODIFICARE IL NOME DEL DB PRIMA DI USARE LO SCRIPT
Use TestMoveTable
Go
--N.B. MODIFICARE IL NOME DELLA TABELLA E DEL FILEGROUP
Declare @TabToMove varchar(128) = '[dbo].[TableFK]'
Declare @FileGroup varchar(128) = 'NEWFG'

Declare @Disable_Index varchar(max) = 'Alter index ALL on ' + @TabToMove + ' Disable;'
Declare @Drop_Clustered_Index varchar(max) = ''
Declare @Create_Clustered_Index varchar(max) = ''
Declare @Rebuild_NonClustered_Index varchar(max) = ''
Declare @Create_Fk varchar(max) = ''
Declare @Drop_Fk varchar(max) = ''
Select @Drop_Fk+= drop_command +';' + char(13) + char(10) From 
(

 select 
  'ALTER TABLE '
  + QUOTENAME(Object_Name(FKC.Parent_object_id)) +  ' DROP CONSTRAINT '
  + QUOTENAME(Object_name(FKC.constraint_object_id)) DROP_COMMAND
 from sys.foreign_key_columns FKC
  Inner Join sys.columns CP
   ON CP.object_id = FKC.parent_object_id 
    AND CP.column_id = FKC.parent_column_id
  Inner Join sys.columns RCP
   ON RCP.object_id = FKC.Referenced_object_id 
    AND RCP.column_id = FKC.Referenced_column_id
  Inner join INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS Const
   ON Const.CONSTRAINT_NAME = Object_name(FKC.constraint_object_id)
 Where FKC.referenced_object_id = Object_ID(@TabToMove)
)Drop_Command


Select @Create_Fk+= create_command +';' + char(13) + char(10) From 
(
 select 
  'ALTER TABLE '
  + QUOTENAME(Object_Name(FKC.Parent_object_id)) +  ' WITH CHECK ADD  CONSTRAINT '
  + QUOTENAME(Object_name(FKC.constraint_object_id)) + ' FOREIGN KEY (' 
  + QUOTENAME(CP.name) + ') REFERENCES ' + QUOTENAME(Object_Name(FKC.Referenced_object_id)) 
  + '(' + QUOTENAME(RCP.name) + ')'
   + CASE WHEN Const.UPDATE_RULE = 'CASCADE' THEN ' ON UPDATE CASCADE ' ELSE '' END
   + CASE WHEN Const.UPDATE_RULE = 'CASCADE' THEN ' ON DELETE CASCADE ' ELSE '' END CREATE_COMMAND

 from sys.foreign_key_columns FKC
  Inner Join sys.columns CP
   ON CP.object_id = FKC.parent_object_id 
    AND CP.column_id = FKC.parent_column_id
  Inner Join sys.columns RCP
   ON RCP.object_id = FKC.Referenced_object_id 
    AND RCP.column_id = FKC.Referenced_column_id
  Inner join INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS Const
   ON Const.CONSTRAINT_NAME = Object_name(FKC.constraint_object_id)
 Where FKC.referenced_object_id = Object_ID(@TabToMove)
)Create_Command

Select @Rebuild_NonClustered_Index = @Rebuild_NonClustered_Index + char(13) +char(10) + Rebuild_Index + ';' From
(
select 'Alter Index '+ Quotename(i.name)  + ' on ' + @TabToMove + ' Rebuild' 
   Rebuild_Index
from sys.indexes as i
  inner join sys.objects obj
    on i.object_id=obj.object_id
  inner join sys.schemas sch
    on obj.schema_id=sch.schema_id
  inner join sys.partitions part
    on i.object_id=part.object_id
     and i.index_id=part.index_id
  inner join sys.stats stats
    on i.object_id=stats.object_id
     and i.index_id=stats.stats_id
where i.object_id = object_id(@TabToMove)
 And I.type_desc = 'NONCLUSTERED'
)A

Select @Drop_Clustered_Index = Drop_Index From
(
 select Case When is_primary_key = 1 Or is_unique_constraint = 1 Then 'Alter Table ' + @TabToMove + ' Drop constraint ' + Quotename(i.name) 
    else 'Drop index ' + Quotename(i.name) + ' on ' + @TabToMove  + ';'
   End  Drop_Index
 from sys.indexes as i
     inner join sys.objects obj
     on i.object_id=obj.object_id
     inner join sys.schemas sch
     on obj.schema_id=sch.schema_id
     inner join sys.partitions part
     on i.object_id=part.object_id
      and i.index_id=part.index_id
     inner join sys.stats stats
     on i.object_id=stats.object_id
      and i.index_id=stats.stats_id
 where i.object_id = object_id(@TabToMove)
  And I.type_desc = 'CLUSTERED'
)A


Select @Create_Clustered_Index = Create_Index from
(
Select Case is_primary_key When 1 Then 'Alter Table ' + @TabToMove + ' Add constraint ' + index_name + ' Primary Key Clustered (' + reverse(stuff(reverse(index_columns), 1, 1, '')) + ') on ' + @FileGroup + ';'
   else 
    Case is_unique_constraint when 1
     Then 'Alter Table ' + @TabToMove + ' Add constraint ' + index_name + ' Unique Clustered (' + reverse(stuff(reverse(index_columns), 1, 1, '')) + ') on ' + @FileGroup + ';'
    else
     Case Is_unique When 1 
      Then 'Create Unique Clustered index ' + Quotename(index_name) + ' on ' + @TabToMove + '(' + reverse(stuff(reverse(index_columns), 1, 1, '')) + ') on ' + @FileGroup + ';'
     Else
      'Create Clustered index ' + Quotename(index_name) + ' on ' + @TabToMove + '(' + reverse(stuff(reverse(index_columns), 1, 1, '')) + ') on ' + @FileGroup + ';'
     End
    End
  End Create_Index 
from
(
 select
 quotename(sch.name)+'.'+quotename(obj.name) [table_name],
 quotename(i.name) [index_name],
 i.type_desc,
 (
     select '['+col.name+'] ' 
          + Case is_descending_key When 1 then ' Desc,' 
           Else 'Asc,'
         End as [data()]
     from sys.index_columns as k
      inner join sys.columns col
          on k.object_id=col.object_id
           and k.column_id=col.column_id
     where k.object_id = i.object_id
      and k.index_id = i.index_id
      and k.index_id<>0
      and k.is_included_column<>1
      order by key_ordinal, k.column_id
     for xml path('')
 )
 as [index_columns],
 is_unique,
 is_primary_key,
 is_unique_constraint,
 fill_factor,
 is_padded
 from sys.indexes as i
     inner join sys.objects obj
     on i.object_id=obj.object_id
     inner join sys.schemas sch
     on obj.schema_id=sch.schema_id
     inner join sys.partitions part
     on i.object_id=part.object_id
      and i.index_id=part.index_id
     inner join sys.stats stats
     on i.object_id=stats.object_id
      and i.index_id=stats.stats_id
 where i.object_id = object_id(@TabToMove)
  And I.type_desc = 'CLUSTERED' 
 )A
)B

Print @Drop_Fk
Print @Disable_Index
Print @Drop_Clustered_Index
Print @Create_Clustered_Index
Print @Rebuild_NonClustered_Index
Print @Create_Fk

Lo script genera un  risultato simile a questo nella finestra Messages :





Attenzione, non eseguire lo script su tabelle partizionate....

Ciao

Luca