How to script Index Names into temp table to load data faster

--IN SOME SCENARIOS WHERE WE NEED TO DROP INDEX BEFORE LOADING DATA AND -- CREATING AFTER. BELOW SCRIPTS WILL HELP YOU.. WE MAY NEED NECESSARY CHANGES WHERE NEEDED

-- FIRST CREATE TABLE TO STORE SCRIPTS TO DELETE AND CREATE INDEXS .

CREATE TABLE [dbo].[TempSqlScriptsS](

      [Id] [int] IDENTITY(1,1) NOT NULL,

      [BtlId] [int] NOT NULL,

      [TableName] [varchar](500) NOT NULL,

      [Create] [bit] NOT NULL,

      [sql] [varchar](max) NOT NULL,

 CONSTRAINT [PK_TempSqlScriptsS] PRIMARY KEY CLUSTERED

(

      [Id] ASC

)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

) ON [PRIMARY]

GO

--CREATE SPROC NOW WHCIH TAKE INPUT AS SCHEMA , TABLE AND Indexid(IF NEEDED)  AND GENETEA ALL INDEXES AVAILABLE ON PARTICULAR TABLE.

  CREATE PROCEDURE [dbo].[uspScriptIndex]

      @STName varchar(50),

      @SSName varchar(50),

      @Btlid int

AS

BEGIN

      SET NOCOUNT ON

     

      -- Get all existing indexes, but NOT the primary keys

      DECLARE cIX CURSOR FOR

            SELECT DISTINCT

                  SCHEMA_NAME(tbl.schema_id) [TableSchema]

                  ,tbl.name AS [TableName]

                  ,i.Object_ID AS [ObjectId]

                  ,i.name AS [IndexName]

                  ,i.Index_ID

                  ,filegroup_name(i.data_space_id) FileGroup

            FROM sys.tables AS tbl

            INNER JOIN sys.indexes i

                  ON (i.index_id > 0 and i.is_hypothetical = 0)

                  AND (i.object_id=tbl.object_id)

            WHERE i.is_unique = 0

                  AND i.is_primary_key = 0

                  AND tbl.name = @STName

                  AND SCHEMA_NAME(tbl.schema_id) = @SSName

      DECLARE @IxSchema SYSNAME

            ,@IxTable SYSNAME

            ,@IxTableID INT

            ,@IxName SYSNAME

            ,@IxID INT

            ,@FileGroup varchar(50)

      -- Loop through all indexes

      OPEN cIX

     

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

     

      WHILE (@@FETCH_STATUS = 0)

      BEGIN

            DECLARE @IXSQL NVARCHAR(4000)

            SET @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 [' + @IxSchema + '].[' + @IxTable + '] ('

            -- 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.Index_Column_ID

            DECLARE @IxColumn SYSNAME

                  ,@IxFirstColumn BIT = 1

                  ,@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.Index_Column_ID

            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)

                        SELECT @IxFirstColumn = 0

                              ,@IXSQL = @IXSQL + 'INCLUDE ('

                  ELSE

                        SET @IXSQL = @IXSQL + ', '

                  SET @IXSQL = @IXSQL + @IxColumn

                  FETCH NEXT FROM cIxIncColumn INTO @IxColumn, @IxIsDescending

                 

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

            END

            SET @IXSQL = @IXSQL + ' WITH (MAXDOP=6) ON [' + @FileGroup + ']'

            CLOSE cIxIncColumn

            DEALLOCATE cIxIncColumn

            INSERT INTO EQMDC_Staging.dbo.TempSqlScriptsS

                  (Btlid

                  ,TableName

                  ,[Create]

                  ,[sql])

           

            SELECT

                  @Btlid Btlid

                  ,@STName TableName

                  ,1 [Create]

                  ,'IF NOT EXISTS(SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N''[' + @IxSchema + '].[' + @IxTable + ']'')

                        AND name = N''' + @IxName + ''')

                        ' + @IXSQL [sql]

           

            UNION ALL

           

            SELECT

                  @Btlid Btlid

                  ,@STName TableName

                  ,0 [Create]

                  ,'IF EXISTS(SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N''[' + @IxSchema + '].[' + @IxTable + ']'')

                        AND name = N''' + @IxName + ''')

                        DROP INDEX [' + @IxName + '] ON [' + @IxSchema + '].[' + @IxTable + '] ' +

                        CASE WHEN INDEXPROPERTY(@IxTableID, @IxName, 'IsClustered') = 1

                              THEN 'WITH (MAXDOP=6)'

                              ELSE ''

                              END [sql]

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

      END

      CLOSE cIX

      DEALLOCATE cIX

END

GO

-- NOW  HOW TO EXECUTE ABOVE S-PROC TO SCRIPT INDEXES ON TABLE

EXEC dbo.uspScriptIndex @STName = 'TableName', @SSName = 'dbo', @Btlid = 3109

 -- NOW YOU CAN USE YOUR TABLE ( [dbo].[TempSqlScriptsS])WHICH CONTAINS INDEX SCRIPT WHERE NECESSARY