How to copy indexes from one table to another temp table

--HOW TO COPY INDEXS FROM ONE TABLE TO ANOTHER TEMP TABLE?

-- BLOW SPROC WILL COPY INDEX FROM ONE TABLE TO ANOTHER TEMP TABLE?

CREATE PROCEDURE [dbo].[uspCopyIndex]

      @STName varchar(50)

      ,@SSName varchar(50)

      ,@DTName varchar(50)

      ,@DSName varchar(50)

      ,@ClusteredOnly bit

AS

BEGIN

      -- Get all existing indexes

      DECLARE cIX CURSOR FOR

            SELECT DISTINCT

                  SCHEMA_NAME(tbl.schema_id) AS [TableSchema],

                  tbl.name AS [TableName]

                  ,i.Object_ID AS [ObjectId],

                  i.name AS [IndexName]

                  ,i.Index_ID

                  ,i.is_primary_key IsPrimaryKey

            FROM sys.tables AS tbl

            INNER JOIN sys.indexes AS i ON (i.index_id > 0 and i.is_hypothetical = 0) AND (i.object_id=tbl.object_id)

            WHERE tbl.name = @STName AND SCHEMA_NAME(tbl.schema_id) = @SSName

                 

      DECLARE @IxSchema SYSNAME

            ,@IxTable SYSNAME

            , @IxTableID INT

            , @IxName SYSNAME

            , @IxID INT

            ,@IsPrimaryKey BIT

      -- Loop through all indexes

      OPEN cIX

      FETCH NEXT FROM cIX INTO @IxSchema, @IxTable, @IxTableID, @IxName, @IxID, @IsPrimaryKey

      WHILE (@@FETCH_STATUS = 0)

      BEGIN

            -- Check if clustered

            IF @ClusteredOnly = 0 OR (@ClusteredOnly = 1 AND INDEXPROPERTY(@IxTableID, @IxName, 'IsClustered') = 1)

            BEGIN

                  DECLARE @IXSQL NVARCHAR(4000)

                 

                  SET @IXSQL = 'IF  NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N''[' + @DSName + '].[' + @DTName + ']'') AND name = N''' + @IxName + ''')' + CHAR(10) + CHAR(13)

                 

                  IF @IsPrimaryKey = 0

                  BEGIN

                        SET @IXSQL = @IXSQL + 'CREATE '

                        -- Check if the index is unique

                        IF (INDEXPROPERTY(@IxTableID, @IxName, 'IsUnique') = 1) SET @IXSQL = @IXSQL + 'UNIQUE '

                        -- Check if the index is clustered

                        IF (INDEXPROPERTY(@IxTableID, @IxName, 'IsClustered') = 1) SET @IXSQL = @IXSQL + 'CLUSTERED '

                              ELSE SET @IXSQL = @IXSQL + 'NONCLUSTERED '

                        SET @IXSQL = @IXSQL + 'INDEX [' + @IxName + '] ON [' + @DSName + '].' + @DTName + ' ('

                  END

                  ELSE IF @IsPrimaryKey = 1

                  BEGIN

                        SET @IXSQL = @IXSQL + 'ALTER TABLE [' + @DSName + '].' + @DTName + ' ADD CONSTRAINT [Aux' + @IxName + '] PRIMARY KEY '

                        IF (INDEXPROPERTY(@IxTableID, @IxName, 'IsClustered') = 1) SET @IXSQL = @IXSQL + 'CLUSTERED ('

                              ELSE SET @IXSQL = @IXSQL + 'NONCLUSTERED ('

                  END

                  -- Get all columns of the index

                  DECLARE cIxColumn CURSOR FOR

                    SELECT SC.Name, IC.is_descending_key

                    FROM Sys.Index_Columns IC

                         JOIN Sys.Columns SC ON IC.Object_ID = SC.Object_ID AND IC.Column_ID = SC.Column_ID

                    WHERE IC.Object_ID = @IxTableID AND Index_ID = @IxID AND IC.is_included_column = 0

                    ORDER BY IC.key_ordinal

                  DECLARE @IxColumn SYSNAME

                  DECLARE @IxFirstColumn BIT SET @IxFirstColumn = 1

                  DECLARE @IxIsDescending BIT

                  -- Loop throug all columns of the index and append them to the CREATE statement

                  OPEN cIxColumn

                  FETCH NEXT FROM cIxColumn INTO @IxColumn, @IxIsDescending

                  WHILE (@@FETCH_STATUS = 0)

                  BEGIN

                        IF (@IxFirstColumn = 1) SET @IxFirstColumn = 0

                        ELSE SET @IXSQL = @IXSQL + ', '

                        SET @IXSQL = @IXSQL + @IxColumn

                        IF (@IxIsDescending = 1) SET @IXSQL = @IXSQL + ' DESC' ELSE SET @IXSQL = @IXSQL + ' ASC'

                    FETCH NEXT FROM cIxColumn INTO @IxColumn, @IxIsDescending

               END

               CLOSE cIxColumn

               DEALLOCATE cIxColumn

            SET @IXSQL = @IXSQL + ') '

                  -- Get all included columns of the index

                  DECLARE cIxIncColumn CURSOR FOR

                    SELECT SC.Name, IC.is_descending_key

                    FROM Sys.Index_Columns IC

                         JOIN Sys.Columns SC ON IC.Object_ID = SC.Object_ID AND IC.Column_ID = SC.Column_ID

                    WHERE IC.Object_ID = @IxTableID AND Index_ID = @IxID AND IC.is_included_column = 1

                    ORDER BY IC.key_ordinal

                  SET @IxFirstColumn = 1

                  -- Loop throug all columns of the index and append them to the CREATE statement

                  OPEN cIxIncColumn

                  FETCH NEXT FROM cIxIncColumn INTO @IxColumn, @IxIsDescending

                  WHILE (@@FETCH_STATUS = 0)

                  BEGIN

                        IF (@IxFirstColumn = 1)

                              BEGIN

                                    SET @IxFirstColumn = 0

                                    SET @IXSQL = @IXSQL + 'INCLUDE ('

                              END

                        ELSE SET @IXSQL = @IXSQL + ', '

                        SET @IXSQL = @IXSQL + @IxColumn

                        FETCH NEXT FROM cIxIncColumn INTO @IxColumn, @IxIsDescending

                        IF (@@FETCH_STATUS != 0) SET @IXSQL = @IXSQL + ') '

                  END

                  CLOSE cIxIncColumn

                  DEALLOCATE cIxIncColumn

                 

                  SET @IXSQL = @IXSQL + ' WITH (MAXDOP=6)'

                  EXEC (@IXSQL)

            END

            FETCH NEXT FROM cIX INTO @IxSchema, @IxTable, @IxTableID, @IxName, @IxID, @IsPrimaryKey

      END

      CLOSE cIX

      DEALLOCATE cIX

END

E.G

--MAKE SURE BOTH TABLE SHOULD HAVE SAME COLUMNS NAME

EXEC EXEC uspCopyIndex

                              @STName = 'FactActivity'

                              ,@SSName = 'dbo'

                              ,@DTName = 'FactActivityAux'

                              ,@DSName = 'dbo'

                              ,@ClusteredOnly = 0