Move one Filegroup to another

/**************************************************************************
* Configuration
*/-- filegroup from which tables will be removed
SET @SRC_FILEGROUP = 'PRIMARY'
-- filegroup to which tables will be moved
SET @DEST_FILEGROUP = 'FG_DATA'
-- schema, for which the action will be done
SET @SCHEMA_NAME = 'stage'
/*
* End Configuration
*************************************************************************/

/**
* Autor:????Tobias Kiemes
* Version:????20090731
*
* Script moves all tables of a specific schema found in a specific filegroup
* to another specific filegroup (specified under "Configuration"
*
* Attention:
* - this script does not work atomic
* - this script can be restarted
* 
* Preconditions:
* - The new filegroup must exist.
* - A Backup should exist
*/

set nocount on

DECLARE @DEST_FILEGROUP sysname
DECLARE @SCHEMA_NAME sysname
DECLARE @SRC_FILEGROUP sysname

/**************************************************************************
* Configuration
*/
-- filegroup from which tables will be removed
SET @SRC_FILEGROUP = 'PRIMARY'

-- filegroup to which tables will be moved
SET @DEST_FILEGROUP = 'FG_DATA'

-- schema, for which the action will be done
SET @SCHEMA_NAME = 'stage'

/* 
* End Configuration
*************************************************************************/
/**************************************************************************
* Step 1: Todo Liste erstellen
*************************************************************************/IF object_id('tempdb..#todo') > 0
drop table #todo

select schema_name(t.schema_id) as table_schema
, t.name as table_name
, i.type_desc organized_as
, ISNULL(ds.name, partition_ds.name) dataspace_name
, ISNULL(dbf.name, partition_dbf.name) datafile_name
, ISNULL(dbf.physical_name, partition_dbf.physical_name) datafile_physical_name
, 0 as verarbeitet
into #todo
from sys.tables t
join sys.indexes i
on t.object_id = i.object_id
and index_id in (0, 1)
join sys.data_spaces ds
on i.data_space_id = ds.data_space_id
left outer join sys.database_files dbf
on dbf.data_space_id = ds.data_space_id
left outer join sys.destination_data_spaces dds
on ds.data_space_id = dds.partition_scheme_id
-- gehe davon aus, das alles partitionen einer partitionsfunktion
-- im gleichen file liegen
and dds.destination_id = 1
left outer join sys.data_spaces partition_ds
on dds.data_space_id = partition_ds.data_space_id
left outer join sys.database_files partition_dbf
on partition_dbf.data_space_id = partition_ds.data_space_id
where schema_name(t.schema_id) = @SCHEMA_NAME
and ISNULL(ds.name, partition_ds.name) = @SRC_FILEGROUP


declare @table_schema sysname
declare @table_name sysname
declare @organized_as sysname
declare @stmt nvarchar(max)
declare @count_heap int
declare @count_clustered int
declare @count_clustered_pk int

SET @count_heap = 0
SET @count_clustered = 0
SET @count_clustered_pk = 0

begin try

/**************************************************************************
* Step 2: Todo Liste iterieren und Tabellen verarbeiten
*************************************************************************/while (select count(*) from #todo where verarbeitet = 0) > 0
begin
select top 1 
@table_schema = table_schema
, @table_name = table_name
, @organized_as = organized_as
from #todo
where verarbeitet = 0

print 'verarbeite ' + @table_schema + '.' + @table_name + '..'
IF @organized_as = 'HEAP'
BEGIN
/* Heap ist einfach
* Es wird ein Pseudo CIX angelegt und dann wieder gelöscht - fertig
*/print ' ist ein Heap'
SET @count_heap = @count_heap + 1

set @stmt = ' alter table '+@table_schema + '.' + @table_name +' add tmp__ bit'
print @stmt
exec (@stmt)

set @stmt = ' CREATE CLUSTERED INDEX [cix_'+@table_schema + '_' + @table_name + ']'
+ ' ON ' +@table_schema + '.' + @table_name + '(tmp__)' + CHAR(13)
+ ' ON [' + @DEST_FILEGROUP + ']'

print @stmt
exec (@stmt)

set @stmt = ' DROP INDEX ['+ @table_schema + '].[' + @table_name + '].[cix_'+@table_schema + '_' + @table_name + ']'
print @stmt
exec (@stmt)

set @stmt = ' alter table '+@table_schema + '.' + @table_name +' drop column tmp__'
print @stmt
exec (@stmt)
END
ELSE IF @organized_as = 'CLUSTERED' 
BEGIN
/* Clustered ist schwieriger. Wir müssen uns den vorhandenen neu
* erzeugen und dazu erst einmal die Details ermitteln.
*/print ' ist clustered' 
SET @count_clustered = @count_clustered + 1 

/**************************************************************************
???? * Step 2.1: CIX kann aus mehreren Spalten bestehen
* Diese ermitteln und Todo-Tabelle erzeugen
???? *************************************************************************/IF object_id('tempdb..#cix') > 0
???????? drop table #cix

????????select schema_name(obj.schema_id) as schema_name
????????????, object_name(idx.object_id) as table_name
????????????, idx.name as index_name
????????????, idx.object_id
????????????, idx.type_desc
????????????, idx.is_unique
???????? -- , i.*
????????????, col.column_id
????????????, tabcol.name as column_name
, is_primary_key
????????????, 0 as verarbeitet
????????into #cix
????????from sys.indexes idx
????????join sys.objects obj
???????? on idx.object_id = obj.object_id
????????join sys.index_columns col
???????? on idx.object_id = col.object_id
???????? and idx.index_id = col.index_id
????????join sys.columns tabcol
???????? on col.object_id = tabcol.object_id
???????? and col.column_id = tabcol.column_id
????????where is_unique_constraint = 0
???????? and schema_name(obj.schema_id) <> 'sys'
???????? and idx.type_desc = 'CLUSTERED'
???????? and schema_name(obj.schema_id) = @table_schema
???????? and object_name(idx.object_id) = @table_name
????????order by 1, 2, 3, 4, 5, 6, 7

DECLARE @stmt_cix varchar(max)
DECLARE @cix_index int
DECLARE @cix_colname sysname
DECLARE @is_primary_key bit
DECLARE @is_unique bit
DECLARE @index_name sysname
DECLARE @i int
SET @cix_index = 0
SET @stmt_cix = ''
SET @i = 0

/**************************************************************************
???? * Step 2.2: CIX Spalten iterieren und CIX-Statement zusammenbauen
???? *************************************************************************/WHILE (select count(*) from #cix where verarbeitet = 0) > 0
BEGIN

select top 1 @cix_colname = column_name
, @is_primary_key = is_primary_key
, @is_unique = is_unique
, @index_name = index_name
from #cix
where verarbeitet = 0

???????? IF @i = 0
BEGIN
SET @stmt_cix = 'CREATE '
IF @is_unique = 1
SET @stmt_cix = @stmt_cix + 'UNIQUE '
SET @stmt_cix = @stmt_cix + 'CLUSTERED INDEX [' + @index_name + ']'
+ ' ON ' 
+ @table_schema + '.' +@table_name
+ ' ('
END

SET @stmt_cix = @stmt_cix + @cix_colname
IF (select count(*) from #cix where verarbeitet = 0) > 1
SET @stmt_cix = @stmt_cix + ', '

update #cix
set verarbeitet = 1
where column_name = @cix_colname

SET @i = @i + 1
END

SET @stmt_cix = @stmt_cix + ') WITH DROP_EXISTING ON [' + @DEST_FILEGROUP + ']' 

IF @is_primary_key = 1
BEGIN
print ' ohje..das ist ein pk!'
SET @count_clustered_pk = @count_clustered_pk + 1
END
print ' CIX-Statement: ' + @stmt_cix
EXEC (@stmt_cix)
END --IF @organized_as = 'CLUSTERED' 
ELSE
BEGIN
RAISERROR(' Speicherform unbekannt!', 13, 1)
END




-- Satz markieren
update #todo
set verarbeitet = 1
where table_schema = @table_schema
and table_name = @table_name
end

end try
begin catch
declare @message varchar(max)
set @message = error_message()
raiserror(@message, 13, 1)
end catch

print ''
print convert(varchar(10), @count_heap) + ' Heaps umkopiert'
print convert(varchar(10), @count_clustered) + ' Clustered Tables umkopiert'
print convert(varchar(10), @count_clustered_pk) + ' davon, waren ein Primary Key'