List All Indexes with Create Index statement

Indexes.sql

SELECT  SchemaName ,
        TableName ,
        IndexName ,
        ( CASE PK
            WHEN 'PK'
            THEN 'ALTER ' + 'TABLE ' + SchemaName + '.' + TableName
                 + ' ADD CONSTRAINT ' + IndexName + ' PRIMARY KEY'
                 + ( CASE SUBSTRING(Type, 3, 1)
                       WHEN 'C' THEN ' CLUSTERED'
                       ELSE ''
                     END )
            ELSE 'CREATE ' + ( CASE SUBSTRING(Type, 1, 1)
                                 WHEN '1' THEN 'UNIQUE '
                                 ELSE ''
                               END ) + ( CASE SUBSTRING(Type, 3, 1)
                                           WHEN 'C' THEN 'CLUSTERED '
                                           ELSE ''
                                         END ) + 'INDEX ' + IndexName + ' ON '
                 + SchemaName + '.' + TableName
          END ) + ' (' + ( CASE WHEN Key1 IS NULL THEN ''
                                ELSE Key1 + ( CASE SUBSTRING(Type, 4 + 1, 1)
                                                WHEN 'D' THEN ' DESC'
                                                ELSE ''
                                              END )
                           END ) + ( CASE WHEN Key2 IS NULL THEN ''
                                          ELSE ', ' + Key2
                                               + ( CASE SUBSTRING(Type, 4 + 2,
                                                              1)
                                                     WHEN 'D' THEN ' DESC'
                                                     ELSE ''
                                                   END )
                                     END ) + ( CASE WHEN Key3 IS NULL THEN ''
                                                    ELSE ', ' + Key3
                                                         + ( CASE SUBSTRING(Type,
                                                              4 + 3, 1)
                                                              WHEN 'D'
                                                              THEN ' DESC'
                                                              ELSE ''
                                                             END )
                                               END )
        + ( CASE WHEN Key4 IS NULL THEN ''
                 ELSE ', ' + Key4 + ( CASE SUBSTRING(Type, 4 + 4, 1)
                                        WHEN 'D' THEN ' DESC'
                                        ELSE ''
                                      END )
            END ) + ( CASE WHEN Key5 IS NULL THEN ''
                           ELSE ', ' + Key5 + ( CASE SUBSTRING(Type, 4 + 5, 1)
                                                  WHEN 'D' THEN ' DESC'
                                                  ELSE ''
                                                END )
                      END ) + ( CASE WHEN Key6 IS NULL THEN ''
                                     ELSE ', ' + Key6
                                          + ( CASE SUBSTRING(Type, 4 + 6, 1)
                                                WHEN 'D' THEN ' DESC'
                                                ELSE ''
                                              END )
                                END ) + ')' AS CreateIndex
FROM    ( SELECT    SCHEMA_NAME(schema_id) AS SchemaName ,
                    OBJECT_NAME(si.object_id) AS TableName ,
                    si.name AS IndexName ,
                    ( CASE is_primary_key
                        WHEN 1 THEN 'PK'
                        ELSE ''
                      END ) AS PK ,
                    ( CASE is_unique
                        WHEN 1 THEN '1'
                        ELSE '0'
                      END ) + ' ' + ( CASE si.type
                                        WHEN 1 THEN 'C'
                                        WHEN 3 THEN 'X'
                                        ELSE 'B'
                                      END ) + ' '
                    + -- B=basic, C=Clustered, X=XML
  ( CASE INDEXKEY_PROPERTY(si.object_id, index_id, 1, 'IsDescending')
      WHEN 0 THEN 'A'
      WHEN 1 THEN 'D'
      ELSE ''
    END )
                    + ( CASE INDEXKEY_PROPERTY(si.object_id, index_id, 2,
                                               'IsDescending')
                          WHEN 0 THEN 'A'
                          WHEN 1 THEN 'D'
                          ELSE ''
                        END )
                    + ( CASE INDEXKEY_PROPERTY(si.object_id, index_id, 3,
                                               'IsDescending')
                          WHEN 0 THEN 'A'
                          WHEN 1 THEN 'D'
                          ELSE ''
                        END )
                    + ( CASE INDEXKEY_PROPERTY(si.object_id, index_id, 4,
                                               'IsDescending')
                          WHEN 0 THEN 'A'
                          WHEN 1 THEN 'D'
                          ELSE ''
                        END )
                    + ( CASE INDEXKEY_PROPERTY(si.object_id, index_id, 5,
                                               'IsDescending')
                          WHEN 0 THEN 'A'
                          WHEN 1 THEN 'D'
                          ELSE ''
                        END )
                    + ( CASE INDEXKEY_PROPERTY(si.object_id, index_id, 6,
                                               'IsDescending')
                          WHEN 0 THEN 'A'
                          WHEN 1 THEN 'D'
                          ELSE ''
                        END ) + '' AS 'Type' ,
                    INDEX_COL(SCHEMA_NAME(schema_id) + '.'
                              + OBJECT_NAME(si.object_id), index_id, 1) AS Key1 ,
                    INDEX_COL(SCHEMA_NAME(schema_id) + '.'
                              + OBJECT_NAME(si.object_id), index_id, 2) AS Key2 ,
                    INDEX_COL(SCHEMA_NAME(schema_id) + '.'
                              + OBJECT_NAME(si.object_id), index_id, 3) AS Key3 ,
                    INDEX_COL(SCHEMA_NAME(schema_id) + '.'
                              + OBJECT_NAME(si.object_id), index_id, 4) AS Key4 ,
                    INDEX_COL(SCHEMA_NAME(schema_id) + '.'
                              + OBJECT_NAME(si.object_id), index_id, 5) AS Key5 ,
                    INDEX_COL(SCHEMA_NAME(schema_id) + '.'
                              + OBJECT_NAME(si.object_id), index_id, 6) AS Key6
          FROM      sys.indexes AS si
                    LEFT JOIN sys.objects AS so ON so.object_id = si.object_id
          WHERE     index_id > 0 -- omit the default heap
                    AND OBJECTPROPERTY(si.object_id, 'IsMsShipped') = 0 -- omit system tables
                    AND NOT ( SCHEMA_NAME(schema_id) = 'dbo'
                              AND OBJECT_NAME(si.object_id) = 'sysdiagrams'
                            ) -- omit sysdiagrams
        ) AS indexes
ORDER BY SchemaName ,
        TableName ,
        IndexName;