83512 Wasserburg,Herrengasse 5 0152 53 55 26 40 Janke@innsql.de Mo-Fr. 09:00-16:00

Tabelle in andere FileGroup verschieben

Show/Hidden sql code

View source
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

(0 Votes)

Uwe Janke

Contact Info

Addresse:
Herrengasse 5, 83512 Wasserburg am Inn

Ruf:
0152-53 55 26 40

Email:
Janke@innsql.de

Wir nutzen Cookies auf unserer Website. Einige von ihnen sind essenziell für den Betrieb der Seite, während andere uns helfen, diese Website und die Nutzererfahrung zu verbessern (Tracking Cookies). Sie können selbst entscheiden, ob Sie die Cookies zulassen möchten. Bitte beachten Sie, dass bei einer Ablehnung womöglich nicht mehr alle Funktionalitäten der Seite zur Verfügung stehen.