Wednesday, 12. September 2007 1:47
One of the things I’ve been doing lately for an upgrade from SQL 2000 to SQL 2005 is restructuring databases, adding filegroups and files to split out the data and non clustered indexes, leaving only the system tables in the primary filegroup.
This is actually fairly simple as we’re just dropping the clustered indexes and recreating them in the new filegroup which moves the data. However we have some tables that don’t have clustered indexes (i.e., HEAP tables) so I wrote a script that moves the HEAP tables into another filegroup. First it checks for an identity column and if one exists, I create a clustered index on the identity column for the new filegroup, then remove the clustered index. If an identity column doesn’t exist, I create one with a clustered index on the new filegroup then drop the index and the column. Fairly simple.
On another note if anyone knows an easy way on how to move text, ntext data between to a different filegroup, let me know.
-- Aaron Lowe
-- 8/24/2007
SET NOCOUNT ON
Print 'Starting'
DECLARE @SchemaName nvarchar(128),
@TableName nvarchar(128),
@SQLcmd nvarchar (1024),
@ColumnName nvarchar(128),
@FullQualTable nvarchar(261),
@FileGroup nvarchar(128)
SELECT @FileGroup = DB_NAME() + '_DATA'
DECLARE HeapTables_CRSR Cursor FOR
select ss.name, so.name
from
sys.indexes si
join sys.objects so on si.object_id = so.object_id
join sys.schemas ss on so.schema_id = ss.schema_id
join sys.database_files sdf on sdf.data_space_id = si.data_space_id
join sys.filegroups sfg on sfg.data_space_id = sdf.data_space_id
where
si.type = 0
and so.type = 'U'
and sfg.name = 'PRIMARY'
order by
so.name
OPEN HeapTables_CRSR
FETCH NEXT FROM HeapTables_CRSR INTO @SchemaName, @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @FullQualTable = '[' + @SchemaName + '].[' + @TableName + ']'
Print 'Currently working on: ' + @FullQualTable
IF EXISTS (SELECT * FROM sys.columns WHERE OBJECT_ID = OBJECT_ID(@FullQualTable)
and is_identity = 1)
BEGIN
SELECT @ColumnName = name from sys.columns WHERE OBJECT_ID =
OBJECT_ID(@FullQualTable) and is_identity = 1
SET @SQLCmd = N'CREATE CLUSTERED INDEX [IX_TempIDent] ON
[' + @SchemaName + '].[' + @TableName + '] ([' +
@ColumnName + '] ASC) ON [' + @FileGroup + '];'
exec sp_executesql @SQLCmd
SET @SQLCmd = N'DROP INDEX [IX_TempIDent] ON [' + @SchemaName + '].[' +
@TableName + '];'
exec sp_executesql @SQLCmd
END
ELSE
BEGIN
SET @SQLCmd = N'ALTER TABLE [' + @SchemaName + '].[' + @TableName
+ '] ADD [TempIdent] int IDENTITY (1,1);'
exec sp_executesql @SQLCmd
SET @SQLCmd = N'CREATE CLUSTERED INDEX [IX_TempIDent] ON [' +
@SchemaName + '].[' + @TableName +
'] ([TempIdent] ASC) ON [' + @FileGroup + '];'
exec sp_executesql @SQLCmd
SET @SQLCmd = N'DROP INDEX [IX_TempIDent] ON [' + @SchemaName + '].[' +
@TableName + '];'
exec sp_executesql @SQLCmd
SET @SQLCmd = N'ALTER TABLE [' + @SchemaName + '].[' + @TableName +
'] DROP COLUMN [TempIdent];'
exec sp_executesql @SQLCmd
END
FETCH NEXT FROM HeapTables_CRSR INTO @SchemaName, @TableName
END
CLOSE HeapTables_CRSR
DEALLOCATE HeapTables_CRSR
Print 'Done'