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.

Solution 1

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

Solution 2

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 

Solution 3

Here's a more concise version updated for SQL Server 2017 and above, and now includes indexes on views:

-- remove sys.tables or sys.views from this CTE as needed
with base_objects as
(   Select Name, object_ID, schema_ID, type_desc
    from sys.tables
    union all
    Select  Name, object_ID, schema_ID, type_desc
    from sys.views
)
  
SELECT
    [Table_name] 
,   tbl.[type_desc]
,   [Table_object_ID] 
,   [Index_name] 
,   idx.Index_ID
,   [Definition] = 'CREATE ' + [unique] + idx.[type_desc] + ' INDEX [' + index_name + '] ON ' 
            + SCHEMA_NAME(schema_id) + '.' + Table_name 
            + ' ( ' +  key_cols + ' )'
            + isnull(' INCLUDE ( ' + inc_cols + ' ) ','')
            + ' WITH (' + [options] + ' )'
            + ' ON [' + dat.name + ']' +  CHAR(13) + CHAR(10) + ' GO'
FROM Sys.Indexes idx
    join base_objects tbl
        on tbl.object_id = idx.object_ID
    join sys.stats stat
        ON  stat.object_id = idx.object_id
        AND stat.stats_id = idx.index_id
    JOIN sys.data_spaces dat
        ON  idx.data_space_id = dat.data_space_id
    cross apply (Select
        [Table_name] = OBJECT_NAME(idx.Object_ID)
    ,   [Table_object_ID] = idx.Object_ID
    ,   [Index_name] = idx.Name
    ,   [unique] = case when is_unique = 1 then 'UNIQUE ' else '' end

    ) labels
    cross apply (Select
        key_cols = string_agg(key_col_name, ', ') collate DATABASE_DEFAULT
    ,   inc_cols = string_agg(inc_col_name, ', ') collate DATABASE_DEFAULT
        from
            sys.index_columns sub_ic
            join sys.columns sub_col
                on sub_col.object_ID = sub_ic.object_id and sub_col.column_id = sub_ic.column_id
            cross apply (Select
                key_col_name = case when is_included_column = 0 then sub_col.name end
            ,   inc_col_name = case when is_included_column = 1 then sub_col.name end
            ) key_inc
        where sub_ic.object_id = idx.object_id and sub_ic.index_id = idx.index_id
            and is_included_column = 0
    ) cols
    cross apply (Select
        options = string_agg([option] + on_off, ', ')
        from (values
          ( 'PAD_INDEX = ' , idx.is_padded)
        , ( 'FILLFACTOR = ', nullif(idx.fill_factor, 0))
        , ( 'IGNORE_DUP_KEY = ', idx.ignore_dup_key)
        , ( 'STATISTICS_NORECOMPUTE = ', stat.no_recompute)
        , ( 'ALLOW_ROW_LOCKS = ', idx.allow_row_locks)
        , ( 'ALLOW_PAGE_LOCKS = ', idx.allow_page_locks)
        ) opts([option], val)
        cross apply (Select
            on_off = case val when 1 then 'ON' when 0 then 'OFF' else CONVERT( CHAR(5), val) end
        ) on_off_calc
    ) options_calc

where idx.name is not null

Solution 4

I may suggest a method for this:

1. Script the tables, without indicis (indexes).
2. recreate the tables in an other database
3. use a sql comparer tool, to make the index creating scripts based on the two database (maybe if you have some tool like that, that tool may already have a feature for this.)

Solution 5

Current versions of SQL Server Management Studio have an option to include indexes in generated scripts. Right click on database name, choose Tasks, Generate Scripts... Follow the dialogue and in advanced options change "Script Indexes" to true. Seems to remember the setting for future use.

View more solutions

Comments

[Mahesh]

The above code is not considering schema hence will mess up if you have other schema in your databases.

[jsgoupil]

It does not handle the INCLUDE properly.

[Ed S.]

Fails for INCLUDE, kind of a deal breaker

[Zef]

This is better than the accepted answer. It includes "INCLUDE", padding, etc.

[Robert]

@Tim Lehner Thank you for the script, it's saved me a bunch of time! Awesome, starting point(landed me on the front steps!) Hey, I wanted to point out a small change that was needed for my case (when I modified it for PK). When it gets "all columns of the index", the Index_Column_ID sometimes didn't follow the ordinal value. Changing it to "IC.key_ordinal" resolved the issue in this case.

[Alpi Murányi]

Hi Colleen and thanks for the contribution. Would you mind adding some explanation to your solution (even as code comments) so that it becomes a true source of knowledge for all readers? Thanks and keep on contributing!

[WiseTechGlobal CTO]

Just needs an update to support SQL 2019 ascending key indexes.

[ShellNinja]

Great script. Much obliged! It appears that the IncludedColumns relies on SET CONCAT_NULL_YIELDS_NULL in order parse out the Includes. Just an FYI for the next person if you've got that set to false (default setting is true).

[Thronk]

Also better than accepted answer because it avoids cursors

[Thronk]

KeyColumns should be limited to WHERE IC1.key_ordinal = 1 else Nonclustered indexes on a partition will include the partition column as a key column and it is not.

[Akaitatsu]

You can put the index scripts as part of the table script in a VS database project. This will allow the deployment process to only generate the scripts for items that changed.

[Morgs]

This is by far a much much simpler way to do this!