Generate script of All the indexes in a database

Question

I want to generate a script of all the indexes in a database(Create Index).

I don't want the index script along with create table script. how can we generate it in SQL Server (2005 or 2008). There needs to be separate script for Clustered and Non Clustered Indexes.

Answer 1

SELECT ' CREATE ' +        CASE              WHEN I.is_unique = 1 THEN ' UNIQUE '             ELSE ''        END +        I.type_desc COLLATE DATABASE_DEFAULT + ' INDEX ' +        I.name + ' ON ' +        SCHEMA_NAME(T.schema_id) + '.' + T.name + ' ( ' +        KeyColumns + ' )  ' +        ISNULL(' INCLUDE (' + IncludedColumns + ' ) ', '') +        ISNULL(' WHERE  ' + I.filter_definition, '') + ' WITH ( ' +        CASE              WHEN I.is_padded = 1 THEN ' PAD_INDEX = ON '             ELSE ' PAD_INDEX = OFF '        END + ',' +        'FILLFACTOR = ' + CONVERT(            CHAR(5),            CASE                  WHEN I.fill_factor = 0 THEN 100                 ELSE I.fill_factor            END        ) + ',' +        -- default value         'SORT_IN_TEMPDB = OFF ' + ',' +        CASE              WHEN I.ignore_dup_key = 1 THEN ' IGNORE_DUP_KEY = ON '             ELSE ' IGNORE_DUP_KEY = OFF '        END + ',' +        CASE              WHEN ST.no_recompute = 0 THEN ' STATISTICS_NORECOMPUTE = OFF '             ELSE ' STATISTICS_NORECOMPUTE = ON '        END + ',' +        ' ONLINE = OFF ' + ',' +        CASE              WHEN I.allow_row_locks = 1 THEN ' ALLOW_ROW_LOCKS = ON '             ELSE ' ALLOW_ROW_LOCKS = OFF '        END + ',' +        CASE              WHEN I.allow_page_locks = 1 THEN ' ALLOW_PAGE_LOCKS = ON '             ELSE ' ALLOW_PAGE_LOCKS = OFF '        END + ' ) ON [' +        DS.name + ' ] ' +  CHAR(13) + CHAR(10) + ' GO' [CreateIndexScript] FROM   sys.indexes I        JOIN sys.tables T             ON  T.object_id = I.object_id        JOIN sys.sysindexes SI             ON  I.object_id = SI.id             AND I.index_id = SI.indid        JOIN (                 SELECT *                 FROM   (                            SELECT IC2.object_id,                                   IC2.index_id,                                   STUFF(                                       (                                           SELECT ' , ' + C.name + CASE                                                                         WHEN MAX(CONVERT(INT, IC1.is_descending_key))                                                                              = 1 THEN                                                                              ' DESC '                                                                        ELSE                                                                              ' ASC '                                                                   END                                           FROM   sys.index_columns IC1                                                  JOIN sys.columns C                                                       ON  C.object_id = IC1.object_id                                                       AND C.column_id = IC1.column_id                                                       AND IC1.is_included_column =                                                            0                                           WHERE  IC1.object_id = IC2.object_id                                                  AND IC1.index_id = IC2.index_id                                           GROUP BY                                                  IC1.object_id,                                                  C.name,                                                  index_id                                           ORDER BY                                                  MAX(IC1.key_ordinal)                                                   FOR XML PATH('')                                       ),                                       1,                                       2,                                       ''                                   ) KeyColumns                            FROM   sys.index_columns IC2                                    --WHERE IC2.Object_id = object_id('Person.Address') --Comment for all tables                            GROUP BY                                   IC2.object_id,                                   IC2.index_id                        ) tmp3             )tmp4             ON  I.object_id = tmp4.object_id             AND I.Index_id = tmp4.index_id        JOIN sys.stats ST             ON  ST.object_id = I.object_id             AND ST.stats_id = I.index_id        JOIN sys.data_spaces DS             ON  I.data_space_id = DS.data_space_id        JOIN sys.filegroups FG             ON  I.data_space_id = FG.data_space_id        LEFT JOIN (                 SELECT *                 FROM   (                            SELECT IC2.object_id,                                   IC2.index_id,                                   STUFF(                                       (                                           SELECT ' , ' + C.name                                           FROM   sys.index_columns IC1                                                  JOIN sys.columns C                                                       ON  C.object_id = IC1.object_id                                                       AND C.column_id = IC1.column_id                                                       AND IC1.is_included_column =                                                            1                                           WHERE  IC1.object_id = IC2.object_id                                                  AND IC1.index_id = IC2.index_id                                           GROUP BY                                                  IC1.object_id,                                                  C.name,                                                  index_id                                                   FOR XML PATH('')                                       ),                                       1,                                       2,                                       ''                                   ) IncludedColumns                            FROM   sys.index_columns IC2                                    --WHERE IC2.Object_id = object_id('Person.Address') --Comment for all tables                            GROUP BY                                   IC2.object_id,                                   IC2.index_id                        ) tmp1                 WHERE  IncludedColumns IS NOT NULL             ) tmp2             ON  tmp2.object_id = I.object_id             AND tmp2.index_id = I.index_id WHERE  I.is_primary_key = 0        AND I.is_unique_constraint = 0            --AND I.Object_id = object_id('Person.Address') --Comment for all tables            --AND I.name = 'IX_Address_PostalCode' --comment for all indexes  

Answer 2

This is an excellent article on SQL Server Central that I've used.

Here is another that you might like better.

If it's not exactly what you're looking for (sorting, filtering) I'm sure it would be easier to edit these than to start from scratch.

Here's the slightly corrected (2019) code from the first example:

-- Get all existing indexes, but NOT the primary keys DECLARE cIX CURSOR FOR     SELECT OBJECT_NAME(SI.Object_ID), SI.Object_ID, SI.Name, SI.Index_ID         FROM Sys.Indexes SI              LEFT JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC ON SI.Name = TC.CONSTRAINT_NAME AND OBJECT_NAME(SI.Object_ID) = TC.TABLE_NAME         WHERE TC.CONSTRAINT_NAME IS NULL             AND OBJECTPROPERTY(SI.Object_ID, 'IsUserTable') = 1         ORDER BY OBJECT_NAME(SI.Object_ID), SI.Index_ID  DECLARE @IxTable SYSNAME DECLARE @IxTableID INT DECLARE @IxName SYSNAME DECLARE @IxID INT  -- Loop through all indexes OPEN cIX FETCH NEXT FROM cIX INTO @IxTable, @IxTableID, @IxName, @IxID WHILE (@@FETCH_STATUS = 0) BEGIN     DECLARE @IXSQL NVARCHAR(4000) SET @IXSQL = ''     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 '      SET @IXSQL = @IXSQL + 'INDEX ' + @IxName + ' ON ' + @IxTable + '('      -- Get all columns of the index     DECLARE cIxColumn CURSOR FOR          SELECT SC.Name         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         ORDER BY IC.Index_Column_ID      DECLARE @IxColumn SYSNAME     DECLARE @IxFirstColumn BIT SET @IxFirstColumn = 1      -- Loop throug all columns of the index and append them to the CREATE statement     OPEN cIxColumn     FETCH NEXT FROM cIxColumn INTO @IxColumn     WHILE (@@FETCH_STATUS = 0)     BEGIN         IF (@IxFirstColumn = 1)             SET @IxFirstColumn = 0         ELSE             SET @IXSQL = @IXSQL + ', '          SET @IXSQL = @IXSQL + @IxColumn          FETCH NEXT FROM cIxColumn INTO @IxColumn     END     CLOSE cIxColumn     DEALLOCATE cIxColumn      SET @IXSQL = @IXSQL + ')'     -- Print out the CREATE statement for the index     IF @IXSQL != '' BEGIN PRINT @IXSQL END      FETCH NEXT FROM cIX INTO @IxTable, @IxTableID, @IxName, @IxID END  CLOSE cIX DEALLOCATE cIX