Aaron Lowe

Move HEAP Tables

By Vendoran on September 12th, 2007

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'