Hallo,
vor Jahren hatte ich mal ein fast 50 kb großes SQL Skript, welches (für SQL Server 2000) jedes Datenbankobjekt in jede beliebiege Dateigruppe verschieben konnte. Es stammte von einer Webseite (SQL Farms?) welche heute nicht mehr existiert.
Das Script war so interessant weil es alle möglichen Indizes (gruppiert und nicht-gruppiert) aber eben auch die Primärschlüssel-Einschränkungen verschieben konnte, und auch mit mehrspaltigen Schlüsseln, absteigend sortierten, mit Fillfactor eingerichteten
usw. Indizes umgehen konnte. Indizes lassen sich ja leicht über DROP_EXISTING verschieben, aber PK-Beschränkungen leider nicht.
Da ich ein solches Tool jetzt wieder häufiger brauche (und das alte nicht mehr finden kann, und auch keine andere Alternative in Google finden konnte) habe ich mal angefangen selber eines zu schreiben...
Falls jemand testen / damit spielen möchte, ich bin für jeden Vorschlag und jede Anregung dankbar und werde versuchen nach und nach die noch fehlenden Details zu ergänzen.
WICHTIG: Dieses Script ist (noch...) nicht geeignet um auf produktiven Datenbanken sicher eingesetzt werden zu können. Bitte unbedingt nur auf Testsystemen, und nur nach eingehender Kontrolle vorher bzw. nachher benutzen! Im Kommentar in den ersten paar
Zeilen steht etwas detaillierter was schon geht und was noch nicht.
Hier mein erster Entwurf, es werden keine Änderungen vorgenommen sondern lediglich ein Script produziert welches dann ggf. ausgeführt werden kann, bzw. durch das SELECT am Ende wird die momentane Verteilung von Objecten zu Filegroups ausgegeben.
-- Parameters needed to run are specified here
DECLARE @table_to_move VARCHAR(250), @destination_filegroup VARCHAR(250)
SET @table_to_move = 'Meldung'
SET @destination_filegroup = 'DATA'
/*
Missing:
- Schema support
- Partitions
- PK columns asc / desc sort order
- PK options fillfactor and similar
Included:
- Basic error handling may need improvement
- Basic Execution as transaction may need improvement
- Drop and re-add of FK constraints
- Multi-column PK and FK constraints
*/
-- Storage for generate and store final sql statement
DECLARE @sql VARCHAR(max), @sql_drop_constraint VARCHAR(max), @sql_create_constraint VARCHAR(max), @sql_temp VARCHAR(max)
SET @sql_drop_constraint = 'BEGIN TRY' + CHAR(13) + CHAR(10)
SET @sql_drop_constraint = @sql_drop_constraint + 'BEGIN TRANSACTION' + CHAR(13) + CHAR(10)
SET @sql_create_constraint = ''
-- Get object_id for table
DECLARE @table_to_move_oid INT
SET @table_to_move_oid = (SELECT [object_id] FROM [sys].[all_objects] WHERE [name] = @table_to_move)
-- Storage for object_ids and names of reference constraints
DECLARE @reference_constraint_oid INT, @reference_constraint VARCHAR(250), @parent VARCHAR(250)
-- Execute and open cursor over constraint list targeting the table to be moved
DECLARE constraint_cursor CURSOR FAST_FORWARD
FOR SELECT [object_id], OBJECT_NAME([object_id]), OBJECT_NAME([parent_object_id])
FROM [sys].[foreign_keys]
WHERE [referenced_object_id] = @table_to_move_oid
OPEN constraint_cursor
FETCH NEXT FROM constraint_cursor INTO @reference_constraint_oid, @reference_constraint, @parent
-- Iterate constraint cursor
WHILE @@FETCH_STATUS = 0 BEGIN
-- Initialize ALTER TABLE statements
SET @sql_drop_constraint = @sql_drop_constraint + 'ALTER TABLE [' + @parent + '] DROP CONSTRAINT [' + @reference_constraint + ']' + CHAR(13) + CHAR(10)
SET @sql_create_constraint = @sql_create_constraint + 'ALTER TABLE [' + @parent + '] WITH CHECK ADD CONSTRAINT [' + @reference_constraint + '] FOREIGN KEY('
-- Storage for names of reference constraint columns
DECLARE @parent_col VARCHAR(250), @reference_col VARCHAR(250)
SET @sql_temp = ''
-- Execute and open cursor over constraint columns
DECLARE columns_cursor CURSOR FAST_FORWARD
FOR SELECT COL_NAME([parent_object_id], [parent_column_id]), COL_NAME([referenced_object_id], [referenced_column_id])
FROM [sys].[foreign_key_columns]
WHERE [constraint_object_id] = @reference_constraint_oid
ORDER BY [constraint_column_id] ASC
OPEN columns_cursor
FETCH NEXT FROM columns_cursor INTO @parent_col, @reference_col
-- Iterate constraint cursor
WHILE @@FETCH_STATUS = 0 BEGIN
SET @sql_create_constraint = @sql_create_constraint + '[' + @parent_col + '],'
SET @sql_temp = @sql_temp + '[' + @reference_col + '],'
FETCH NEXT FROM columns_cursor INTO @parent_col, @reference_col
END
-- Free columns cursor
CLOSE columns_cursor
DEALLOCATE columns_cursor
-- Truncate last trailing comma on column list
SET @sql_temp = LEFT(@sql_temp, LEN(@sql_temp) - 1)
SET @sql_create_constraint = LEFT(@sql_create_constraint, LEN(@sql_create_constraint) - 1)
-- Add column lists in ALTER ADD statement
SET @sql_create_constraint = @sql_create_constraint + ') REFERENCES ' + @table_to_move + '(' + @sql_temp + ')' + CHAR(13) + CHAR(10)
-- Continue on next constraint
FETCH NEXT FROM constraint_cursor INTO @reference_constraint_oid, @reference_constraint, @parent
END
-- Free constraint cursor
CLOSE constraint_cursor
DEALLOCATE constraint_cursor
-- Finalize drop and create statements
SET @sql_drop_constraint = @sql_drop_constraint + CHAR(13) + CHAR(10)
SET @sql_create_constraint = @sql_create_constraint
-- Storage for names of reference constraint columns
DECLARE @index_col VARCHAR(250)
SET @sql_temp = ''
-- Execute and open cursor over index columns
DECLARE columns_cursor CURSOR FAST_FORWARD
FOR SELECT COL_NAME([object_id], [column_id])
FROM [sys].[index_columns]
WHERE [object_id] = @table_to_move_oid AND [index_id] = 1
ORDER BY [key_ordinal] ASC
OPEN columns_cursor
FETCH NEXT FROM columns_cursor INTO @index_col
-- Iterate constraint cursor
WHILE @@FETCH_STATUS = 0 BEGIN
SET @sql_temp = @sql_temp + '[' + @index_col + '],'
FETCH NEXT FROM columns_cursor INTO @index_col
END
-- Free columns cursor
CLOSE columns_cursor
DEALLOCATE columns_cursor
-- Truncate last trailing comma on column list
SET @sql_temp = LEFT(@sql_temp, LEN(@sql_temp) - 1)
-- Drop PK constraint and recreate on new FG
DECLARE @index VARCHAR(250)
SET @index = (SELECT [name] FROM [sys].[indexes] WHERE [object_id] = @table_to_move_oid AND [index_id] = 1)
SET @SQL = 'ALTER TABLE [' + @table_to_move + '] DROP CONSTRAINT [' + @index + ']' + CHAR(13) + CHAR(10) + CHAR(13) + CHAR(10)
SET @SQL = @SQL + 'ALTER TABLE [' + @table_to_move + '] ADD CONSTRAINT [' + @index + '] PRIMARY KEY CLUSTERED (' + @sql_temp + ') ON [' + @destination_filegroup + ']'
SET @SQL = @SQL + CHAR(13) + CHAR(10) + CHAR(13) + CHAR(10)
-- Construct final statement and return
SET @sql = @sql_drop_constraint + @sql + @sql_create_constraint
SET @sql = @sql + 'END TRY' + CHAR(13) + CHAR(10)
SET @sql = @sql + 'BEGIN CATCH' + CHAR(13) + CHAR(10)
SET @sql = @sql + 'ROLLBACK TRANSACTION' + CHAR(13) + CHAR(10)
SET @sql = @sql + 'PRINT ''Operation failed and was rolled back''' + CHAR(13) + CHAR(10)
SET @sql = @sql + 'END CATCH' + CHAR(13) + CHAR(10)
SET @sql = @sql + 'IF @@TRANCOUNT > 0 BEGIN' + CHAR(13) + CHAR(10)
SET @sql = @sql + 'COMMIT TRANSACTION' + CHAR(13) + CHAR(10)
SET @sql = @sql + 'PRINT ''Operation complete''' + CHAR(13) + CHAR(10)
SET @sql = @sql + 'END'
PRINT @sql
SELECT
[all_objects].[name] AS [object_name]
, ISNULL([indexes].[name], 'HEAP') AS [index_name]
, [filegroups].[name] AS [filegroup_name]
FROM sys.indexes
INNER JOIN sys.filegroups
ON indexes.data_space_id = filegroups.data_space_id
INNER JOIN sys.all_objects
ON indexes.[object_id] = all_objects.[object_id]
WHERE indexes.data_space_id = filegroups.data_space_id
AND [all_objects].[type] = 'U'
AND [all_objects].[name] <> 'sysdiagrams'
