Move non-clustered indexes (2005/2008)

---------------------------------------------------------------------------
--    Moving non-clustered indexes from one filegroup to another
--    SQL Server 2005/2008 version
--    By Gregory Shadur (TheMOTU)
--    greg_shadur@hotmail.com
--
--    Execute the script in the context of a specific database
--    Generates DROP/CREATE INDEX script
---------------------------------------------------------------------------
ET NOCOUNT ON

DECLARE
    @table_schema_name nvarchar(128),
    @table_name nvarchar(128),
    @index_name nvarchar(128),
    @key_ordinal smallint,
    @column_name nvarchar(128),
    @sort_order varchar(50),
    @is_included_column int,
    @index_type_desc varchar(50),
    @unique_type varchar(50),
    @ignore_dup_key varchar(50),
    @fill_factor tinyint,
    @is_padded varchar(50),
    @allow_row_locks varchar(50),
    @allow_page_locks varchar(50),
-------------------------------------------    
    @table_schema_name_ws nvarchar(128),
    @table_name_ws nvarchar(128),
    @index_name_ws nvarchar(128),
    @key_ordinal_ws smallint,
-------------------------------------------    
    @text1_create varchar(800),
    @text1_create_columns varchar(800),
    @text1_create_end varchar(50),
    @text2_include_start varchar(800),
    @text2_include_columns varchar(50),
    @text2_include_end varchar(50),
    @text4_with varchar(800),
    @text4_with_end varchar(50)

SET @text1_create_end = ')'
SET @text2_include_start = 'INCLUDE ('
SET @text2_include_end = ')'
SET @text4_with_end = ')'
    
DECLARE C01 CURSOR FOR
    SELECT
        S.name AS table_schema_name,
        O.name AS table_name,
        I.name AS index_name,
        CASE IC.key_ordinal WHEN 0 THEN 999 ELSE IC.key_ordinal END AS key_ordinal,
        C.name AS column_name,
        CASE IC.is_descending_key WHEN 0 THEN 'ASC' ELSE 'DESC' END AS sort_order,
        IC.is_included_column,
        I.type_desc AS index_type_desc,
        CASE I.is_unique WHEN 1 THEN 'UNIQUE' ELSE '' END AS unique_type,
        CASE I.ignore_dup_key WHEN 1 THEN 'ON' ELSE 'OFF' END AS ignore_dup_key,
        I.fill_factor,
        CASE I.is_padded WHEN 1 THEN 'ON' ELSE 'OFF' END AS is_padded,
        CASE I.allow_row_locks WHEN 1 THEN 'ON' ELSE 'OFF' END AS allow_row_locks,
        CASE I.allow_page_locks WHEN 1 THEN 'ON' ELSE 'OFF' END AS allow_page_locks
    FROM 
        sys.indexes I 
            INNER JOIN
        sys.objects O
            ON O.object_id = I.object_id AND O.type_desc = 'USER_TABLE'
            INNER JOIN
        sys.schemas S
            ON S.schema_id = O.schema_id
            INNER JOIN
        sys.data_spaces D
            ON D.data_space_id = I.data_space_id AND D.name = 'PRIMARY' -- Old filrgroup name
            INNER JOIN
        sys.index_columns IC
            ON IC.object_id = I.object_id AND IC.index_id = I.index_id
            INNER JOIN
        sys.columns C
            ON C.object_id = IC.object_id AND C.column_id = IC.column_id
    WHERE
        I.type_desc IN ('NONCLUSTERED')
    AND I.is_hypothetical = 0
    AND    I.is_primary_key = 0        -- Exclude primary key constraints
    AND I.is_unique_constraint = 0    -- Exclude unique constraints
    AND I.is_disabled <> 1            -- Exclude disabled indexes
    ORDER BY 1, 2, 3, 4

OPEN C01
FETCH C01 INTO
    @table_schema_name,
    @table_name,
    @index_name,
    @key_ordinal,
    @column_name,
    @sort_order,
    @is_included_column,
    @index_type_desc,
    @unique_type,
    @ignore_dup_key,
    @fill_factor,
    @is_padded,
    @allow_row_locks,
    @allow_page_locks

SELECT @table_schema_name_ws = @table_schema_name
SELECT @table_name_ws = @table_name
SELECT @index_name_ws = @index_name

WHILE @@FETCH_STATUS = 0

    BEGIN
        WHILE @@FETCH_STATUS = 0 
                AND @table_schema_name_ws = @table_schema_name
            BEGIN
                WHILE @@FETCH_STATUS = 0 
                        AND @table_schema_name_ws = @table_schema_name 
                        AND @table_name_ws = @table_name
                    BEGIN
                    
                        SET @text1_create_columns = ''
                        SET @text2_include_columns = ''
                        
                        PRINT 
                            'DROP INDEX [' + 
                            @index_name_ws + '] ON [' + 
                            @table_schema_name_ws + '].[' + 
                            @table_name_ws + '];'
                        PRINT ' '
                            
                        SELECT @text1_create = 
                            'CREATE ' + 
                            @unique_type + 
                            ' ' + @index_type_desc + 
                            ' INDEX [' + 
                            @index_name_ws + '] ON [' +
                            @table_schema_name_ws + '].[' + 
                            @table_name_ws + '] ('
                            
                        SELECT @text4_with = 
                            'WITH (PAD_INDEX = ' + @is_padded + ', STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = ON, ' +
                            'IGNORE_DUP_KEY = ' + @ignore_dup_key + ', ONLINE = OFF, ' +
                            'FILLFACTOR = ' + CAST(@fill_factor AS VARCHAR(50)) + ', ' +
                            'ALLOW_ROW_LOCKS = ' + @allow_row_locks + ', ' + 
                            'ALLOW_PAGE_LOCKS = ' + @allow_page_locks

                        WHILE @@FETCH_STATUS = 0 
                                AND @table_schema_name_ws = @table_schema_name 
                                AND @table_name_ws = @table_name 
                                AND @index_name_ws = @index_name
                            BEGIN
                                IF @is_included_column = 1
                                    BEGIN
                                        SELECT @text2_include_columns = @text2_include_columns + @column_name + ','
                                    END
                                ELSE
                                    BEGIN
                                        SELECT @text1_create_columns = @text1_create_columns + @column_name + ','
                                    END
                            
                                FETCH C01 INTO
                                    @table_schema_name,
                                    @table_name,
                                    @index_name,
                                    @key_ordinal,
                                    @column_name,
                                    @sort_order,
                                    @is_included_column,
                                    @index_type_desc,
                                    @unique_type,
                                    @ignore_dup_key,
                                    @fill_factor,
                                    @is_padded,
                                    @allow_row_locks,
                                    @allow_page_locks
                                                                                            
                            END
                        IF @@FETCH_STATUS = 0 SELECT @index_name_ws = @index_name
                        
                        SELECT @text1_create_columns = '    ' + SUBSTRING(@text1_create_columns, 1, (DATALENGTH(@text1_create_columns) - 1))
                        PRINT @text1_create
                        PRINT @text1_create_columns
                        PRINT @text1_create_end
                        IF DATALENGTH(@text2_include_columns) > 0
                            BEGIN
                                PRINT @text2_include_start
                                PRINT '        ' + SUBSTRING(@text2_include_columns, 1, (DATALENGTH(@text2_include_columns) - 1))
                                PRINT @text2_include_end
                                
                            END
                        PRINT @text4_with
                        PRINT @text4_with_end
                        PRINT 'ON INDEX;' -- New filegroup name
                        PRINT '----'
                    
                    END
                IF @@FETCH_STATUS = 0 SELECT @table_name_ws = @table_name
            
            END
        IF @@FETCH_STATUS = 0 
    SELECT @table_schema_name_ws = @table_schema_name
    
    
    END

CLOSE C01
DEALLOCATE C01