CREATE PROCEDURE [dbo].[proc_MoveTableToFileGroup] ( @fileGroup SYSNAME,
@tableName SYSNAME )
AS
BEGIN
DECLARE @data_space_id INT
DECLARE @OBJECT_ID INT
DECLARE @index_id INT
DECLARE @index_name SYSNAME
DECLARE @OBJECT_NAME SYSNAME
DECLARE @fileGroupName SYSNAME
DECLARE @index_cols NVARCHAR(4000)
DECLARE @SQL NVARCHAR(4000)
DECLARE @key_ordinal INT
SET @index_id = 0
SET @key_ordinal = 0
--- Existiert die gewünschte Gruppe
SELECT @data_space_id = data_space_id ,
@fileGroupName = NAME
FROM sys.filegroups
WHERE NAME = @fileGroup
IF @data_space_id IS NULL
BEGIN
RAISERROR ( 'Die Filegroup existiert nicht.', 16, 1 )
RETURN
END
-- STEP 2: Index Infos der Tabelle abrufen
WHILE 1 = 1
BEGIN
-- STEP 3: Nächster Index
SELECT TOP 1
@OBJECT_ID = i.object_id ,
@index_id = i.index_id ,
@index_name = i.NAME ,
@OBJECT_NAME = o.NAME
FROM sys.indexes AS i
INNER JOIN sys.objects AS o
ON i.object_id = o.object_id
WHERE i.index_id >
0
AND
o.TYPE = 'U'
AND
o.NAME = @tableName
AND
i.index_id >
@index_id
AND
i.data_space_id <
>
@data_space_id ORDER BY i.index_id
-- STEP 4: Fertig, keine weiteren Indizies
IF @@ROWCOUNT = 0
BEGIN
IF @index_id = 0
PRINT 'keine Indizies gefunden die verschoben werden müssten ' + @fileGroupName + ' for table ' + @tableName BREAK
END
SET @index_cols = NULL
SET @key_ordinal = 0
-- STEP 5: index Spalten generieren
WHILE 1 = 1
BEGIN
SELECT TOP 1
@index_cols =
CASE
WHEN @index_cols IS NULL THEN '[' + c.NAME + ']'
ELSE @index_cols + ', [' + c.NAME + ']'
END +
CASE
WHEN i.is_descending_key = 0 THEN ' asc'
ELSE 'desc'
END ,
@key_ordinal = i.key_ordinal
FROM sys.index_columns i
INNER JOIN sys.COLUMNS AS c
ON i.object_id = c.object_id
AND i.column_id = c.column_id
WHERE i.object_id = @OBJECT_ID
AND i.index_id = @index_id
AND i.key_ordinal >
@key_ordinal ORDER BY i.key_ordinal
IF @@ROWCOUNT = 0 BREAK
END
SELECT @SQL =
-- drop/add primary key
CASE
WHEN i.is_primary_key = 1 THEN N'begin try ' + N'begin tran ' + N'alter table [' + @OBJECT_NAME + '] drop constraint [' + i.NAME + '] ' + N'alter table [' + @OBJECT_NAME + '] add constraint [' + i.NAME + '] ' + 'primary key ' +
CASE
WHEN i.TYPE = 1 THEN 'clustered '
ELSE 'nonclustered '
END + ' (' + @index_cols + ') ' + 'with (' + 'IGNORE_DUP_KEY = ' +
CASE
WHEN i.ignore_dup_key = 1 THEN 'ON '
ELSE 'OFF '
END + ', PAD_INDEX = ' +
CASE
WHEN i.is_padded = 1 THEN 'ON '
ELSE 'OFF '
END +
CASE
WHEN i.fill_factor = 0 THEN ''
ELSE ', FILLFACTOR = ' + CONVERT(NVARCHAR(10), i.fill_factor)
END + ') ' + 'ON [' + @fileGroupName + ']' + N' commit ' + N'end try ' + N'begin catch ' + N'rollback ' + N'end catch '
ELSE
-- create index / drop existing
N'create ' +
CASE
WHEN i.is_unique = 1 THEN 'unique '
ELSE ''
END +
CASE
WHEN i.TYPE = 1 THEN 'clustered '
ELSE 'nonclustered '
END + 'index [' + i.NAME + '] on [' + @OBJECT_NAME + '] (' + @index_cols + ') ' + 'with (' + 'IGNORE_DUP_KEY = ' +
CASE
WHEN i.ignore_dup_key = 1 THEN 'ON '
ELSE 'OFF '
END + ', PAD_INDEX = ' +
CASE
WHEN i.is_padded = 1 THEN 'ON '
ELSE 'OFF '
END +
CASE
WHEN i.fill_factor = 0 THEN ''
ELSE ', FILLFACTOR = ' + CONVERT(NVARCHAR(10), i.fill_factor)
END + ', DROP_EXISTING = ON ) ' + 'ON [' + @fileGroupName + ']'
END
FROM sys.indexes AS i
WHERE i.object_id = @OBJECT_ID
AND i.index_id = @index_id
PRINT 'verschiebe index ' + @index_name + ' zu Filegroup ' + @fileGroupName
PRINT '---------------'
PRINT @SQL
PRINT '---------------'
-- und Ausführen
EXEC sp_executesql @SQL
END