SQL Script to copy indexes from the old table to a new one

gistfile1.sql

/******************************************************************************
* Script to copy indexes from the old table to a new one                      *
* Created By Ed Botzum                                                        *
* Created on 10/27/2010                                                       *
*******************************************************************************/

Declare @table_name as nvarchar(100)
Declare @new_table_name as nvarchar(100)
Declare @index_name as nvarchar(100)
Declare @column_name as nvarchar(100)
Declare @column_number as decimal(3,0)
Declare @column_list as nvarchar(1000)

/************************************************/
/************* SET THE TABLE NAME ***************/
Set @table_name = 'AccountComments'

/********************************************************/
/*************** DO NOT MODIFY BELOW HERE ***************/
/********************************************************/

Set @new_table_name = @table_name + '-upgrade'

DECLARE IndexCursor CURSOR FAST_FORWARD FOR

  SELECT sys.indexes.name as index_name
  FROM sys.objects
  INNER JOIN sys.indexes ON sys.objects.object_id = sys.indexes.object_id
  WHERE sys.objects.name = @table_name
    AND sys.indexes.is_primary_key = 0
  GROUP BY sys.indexes.name

OPEN IndexCursor
FETCH NEXT FROM IndexCursor INTO @index_name

WHILE @@FETCH_STATUS = 0
BEGIN

  DECLARE ColCursor CURSOR FAST_FORWARD FOR
    SELECT sys.columns.name as column_name
    FROM sys.objects
    INNER JOIN sys.indexes ON sys.objects.object_id = sys.indexes.object_id
    INNER JOIN sys.index_columns 
            ON sys.index_columns.object_id = sys.indexes.object_id
           AND sys.index_columns.index_id = sys.indexes.index_id
    INNER JOIN sys.columns
            ON SYS.COLUMNS.OBJECT_ID = SYS.INDEX_COLUMNS.OBJECT_ID
           AND sys.columns.column_id = sys.index_columns.column_id
    WHERE sys.objects.name = @table_name
      AND sys.indexes.name = @index_name
      AND sys.indexes.is_primary_key = 0

  OPEN ColCursor
  FETCH NEXT FROM ColCursor INTO @column_name
  SET @column_list = ''
  SET @column_number = 0

  WHILE @@FETCH_STATUS = 0
  BEGIN
    IF @column_number > 0
      SET @column_list = @column_list + ', '
    SET @column_list = @column_list + '[' + @column_name + ']'
    SET @column_number = @column_number + 1
    FETCH NEXT FROM ColCursor INTO @column_name
  END
  CLOSE ColCursor
  DEALLOCATE ColCursor

  PRINT 'CREATE INDEX ' + @index_name + ' ON ' + @new_table_name + ' (' + @column_list + ')'

  FETCH NEXT FROM IndexCursor
  INTO @index_name
END

CLOSE IndexCursor
DEALLOCATE IndexCursor