SQL

;WITH ObjectData AS (

        SELECT o.[object_id], o.[name], o.[type], o.[schema_id], s.[name] AS [schema]

        FROM sys.objects o

        INNER JOIN sys.schemas s

                ON o.[schema_id] = s.[schema_id]

IndexData AS (

        SELECT i.[object_id], i.[index_id], i.[type_desc], i.[name] COLLATE DATABASE_DEFAULT AS [name], i.[is_unique], ds.[name] AS [scheme], ds.[type] AS [scheme_type]

        FROM sys.indexes i

        INNER JOIN sys.data_spaces ds

                ON i.[data_space_id] = ds.[data_space_id]

ColumnData AS (

        SELECT ic.[object_id], ic.[index_id], ic.[index_column_id], ic.[partition_ordinal], ic.[is_descending_key], c.[name]

        FROM sys.index_columns ic

        INNER JOIN sys.COLUMNS c

                ON ic.[object_id] = c.[object_id]

                AND ic.[column_id] = c.[column_id]

COLUMNS AS (

        SELECT i.[object_id], i.[index_id],

                   CAST(LEFT(a.[COLUMNS], len(a.[COLUMNS]) - 1) AS varchar(8000)) AS COLUMNS ,

                   CAST(LEFT(b.[COLUMNS], len(b.[COLUMNS]) - 1) AS varchar(8000)) AS partition_columns

        FROM IndexData i

        CROSS APPLY (

                SELECT c.[name] + CASE WHEN c.[is_descending_key] = 1 THEN 'DESC' ELSE '' END + ', '

                FROM ColumnData c

                WHERE i.[object_id] = c.[object_id]

                  AND i.[index_id]  = c.[index_id]

                ORDER BY c.[index_column_id]

                FOR XML PATH('')

        ) a (COLUMNS)

        OUTER APPLY (

                SELECT c.[name] + ', '

                FROM ColumnData c

                WHERE i.[object_id] = c.[object_id]

                  AND i.[index_id]  = c.[index_id]

                  AND c.[partition_ordinal] > 0

                ORDER BY c.[partition_ordinal]

                FOR XML PATH('')

        ) b (COLUMNS))

SELECT i.[object_id],

           i.[name] AS [index_name],

           i.[type_desc] AS [index_type],

           CAST (

                        'CREATE ' + CASE WHEN i.is_unique = 1 THEN 'UNIQUE ' ELSE '' END +

                    i.[type_desc] + ' INDEX [' + i.[name] + '] ON [' + o.[schema] + '].[' + o.[name] + '] (' +

                        c.[COLUMNS] + ') ON [' + i.[scheme] + ']' +

                        CASE WHEN i.[scheme_type] = 'PS' THEN '(' + c.[partition_columns] + ')' ELSE '' END

             AS varchar(8000)

           )

FROM IndexData i

INNER JOIN ObjectData o

        ON i.[object_id] = o.[object_id]

INNER JOIN COLUMNS c

        ON i.[object_id] = c.[object_id]

        AND i.[index_id] = c.[index_id]

WHERE i.[object_id] = object_id('dbo.WF_QIC_FACT')

 AND i.[index_id] = 1