Dynamic Insert Statement for all Clustered & Non Clustered Indexes

Try this


SELECT
'CREATE '+
    CASE WHEN sys.indexes.is_unique = 1 AND sys.indexes.is_primary_key = 0 THEN 'UNIQUE ' ELSE '' END +
    CASE WHEN sys.indexes.type_desc = 'CLUSTERED' THEN 'CLUSTERED ' ELSE 'NONCLUSTERED ' END
+'INDEX '
+ sys.indexes.name
+ ' ON '
+ sys.objects.name
+ ' ( ' + Index_Columns.index_columns_key +' ) '
+ ISNULL('INCLUDE ('+Index_Columns.index_columns_include+')','') A

FROM
    sys.objects
    JOIN sys.schemas ON sys.objects.schema_id=sys.schemas.schema_id
    JOIN sys.indexes ON sys.indexes.object_id=sys.objects.object_id
    CROSS APPLY (
        SELECT
            LEFT(index_columns_key, LEN(index_columns_key)-1) AS index_columns_key,
            LEFT(index_columns_include, LEN(index_columns_include)-1) AS index_columns_include
        FROM
            (
                SELECT
                    (
                        SELECT sys.columns.name + ','
                        FROM
                            sys.index_columns
                            JOIN sys.columns ON
                                sys.index_columns.column_id=sys.columns.column_id
                                AND sys.index_columns.object_id=sys.columns.object_id
                        WHERE
                            sys.index_columns.is_included_column=0
                            AND sys.indexes.object_id=sys.index_columns.object_id AND sys.indexes.index_id=sys.index_columns.index_id
                        ORDER BY key_ordinal
                        FOR XML PATH('')
                    ) AS index_columns_key,
                    (
                        SELECT sys.columns.name + ','
                        FROM
                            sys.index_columns
                            JOIN sys.columns ON
                                sys.index_columns.column_id=sys.columns.column_id
                                AND sys.index_columns.object_id=sys.columns.object_id
                        WHERE
                            sys.index_columns.is_included_column=1
                            AND sys.indexes.object_id=sys.index_columns.object_id AND sys.indexes.index_id=sys.index_columns.index_id
                        ORDER BY index_column_id
                        FOR XML PATH('')
                    ) AS index_columns_include
            ) AS Index_Columns
    ) AS Index_Columns
WHERE
    sys.objects.type='u'
    AND sys.objects.is_ms_shipped=0
    AND sys.indexes.type_desc <> 'HEAP'
    AND sys.indexes.is_primary_key = 0

Insert Data from procedure Output

Sample Code:

CREATE TABLE #RecurrenceEventData (EventID int,StartDate datetime, StartTime varchar(8), EndDate datetime, EndTime varchar(8))


INSERT #RecurrenceEventData EXEC Get_Recurrence_Events 4,@CityID,@CategoryID,@Keyword,@Neighborhood,@Sort,@FromDate,@ToDate

Output from Get_Recurrence_Events procedure will be saved in #RecurrenceEventData 

Create PROCEDURE [dbo].[Get_RecurrenceEvents]

@FromDate datetime = NULL,

@ToDate datetime = NULL

AS

BEGIN

CREATE TABLE #RecurrenceEvent(EventID int,StartDate datetime,StartTime varchar(8),EndDate datetime,EndTime varchar(8));

SET @NewDate = GETDATE();

INSERT INTO #RecurrenceEvent

SELECT  

Events.EventID, EventDates.Date,RecurrenceInfo.StartTime,RecurrenceInfo.EndDate 

FROM 

Events 

Where (CONVERT(CHAR(10),EventDates.Date ,120) BETWEEN CONVERT(CHAR(10),@FromDate ,120) AND CONVERT(CHAR(10),@ToDate ,120))

SELECT * FROM #RecurrenceEvent

    DROP TABLE #RecurrenceEvent

END