Reputation: 701

Generate CREATE INDEX statements in SQL Server

Does anyone have a script to list of CREATE INDEX statements for all existing indexes in a SQL Server database?

This thread List of all index & index columns in SQL Server DB has great tips on how to find them. But a script to generate the CREATE INDEX statements would be great. Sometimes we come into a situation without adequate data, or indexes have been added in an ad-hoc manner over time without documentation, so the create statements are missing. Like in a situation I find myself in right now.

Thanks.

Upvotes: 6

Answers (5)

that_roy

Reputation: 104

I wrote the following that gets the create statements for table indexes for SQL Server database.

-- Primary Key
SELECT
    -- DISTINCT
    CONCAT(
        'if exists ',
        '( ',
            'select * from sys.indexes where name =''',SI.NAME,''' ',
        ') ',
        'BEGIN ',
            'DROP INDEX ',ST.NAME,'.',SI.NAME,' ',
        'END; ',
        ' ',
        'if NOT exists ',
        '( ',
            'select * from sys.indexes where name =''',SI.NAME,''' ',
        ') ',
        'BEGIN ',
            'ALTER TABLE [',
            SCH.NAME,
            '].[',
            ST.NAME,
            '] ADD CONSTRAINT [',
            SI.NAME,
            '] PRIMARY KEY ',
            -- CASE WHEN SI.is_unique = 1 THEN 'UNIQUE ' END,
            SI.TYPE_DESC COLLATE SQL_Latin1_General_CP1_CI_AS, 
            CHAR(10),char(13),
            ' (',
                CD.COLUMNS,
            ')',
            CASE WHEN LEN(CD2.COLUMNS) <> 0
                THEN
                    CONCAT(
                        'INCLUDE(',
                        CD2.COLUMNS,
                        ')'
                    )
                END,
            ' WITH (',
                LEFT(SI2.PARAMS,LEN(SI2.PARAMS)-1),
            ') ON [PRIMARY]',
        'END;'
    ) CREATE_STATEMENT,
    1 LastColumn
FROM SYS.indexes SI
    outer apply(
        select
            CONCAT(
                CASE WHEN SI.is_padded = 1 THEN 'PAD_INDEX = ON, ' ELSE 'PAD_INDEX = OFF,' END,
                'STATISTICS_NORECOMPUTE = OFF, ',
                'SORT_IN_TEMPDB = OFF, ',
                'ONLINE = OFF, ',
                CASE WHEN SI.allow_row_locks = 1 THEN 'ALLOW_ROW_LOCKS = ON, ' ELSE 'ALLOW_ROW_LOCKS = OFF, 'END,
                CASE WHEN SI.allow_page_locks = 1 THEN 'ALLOW_PAGE_LOCKS = ON, ' ELSE 'ALLOW_PAGE_LOCKS = OFF, ' END,
                CASE WHEN SI.fill_factor <> 0 THEN CONCAT('FILLFACTOR = ', SI.fill_factor,', ') END,
                CASE WHEN SI.OPTIMIZE_FOR_SEQUENTIAL_KEY <> 0 THEN 'OPTIMIZE_FOR_SEQUENTIAL_KEY = ON, ' ELSE 'OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF, ' END
            ) PARAMS
        FROM
            SYS.INDEXES SI2
        WHERE
            1=1
            AND SI2.OBJECT_ID = SI.OBJECT_ID
            AND SI2.INDEX_ID = SI.INDEX_ID
    ) SI2
    INNER JOIN SYS.TABLES ST ON
        1=1
        and ST.object_id = SI.object_id
    inner join sys.schemas sch on
        1=1
        and sch.schema_id = st.schema_id
    -- Columns in index
    outer apply (
        select
            STRING_AGG(CONCAT(
                '[',
                SC2.NAME,
                '] ',
                case when sic2.is_descending_key  = 1 then 'DESC' else 'ASC' end
            ),',') WITHIN GROUP ( ORDER BY sic2.KEY_ORDINAL, sic2.COLUMN_ID) COLUMNS
        from 
            sys.index_columns sic2
            INNER JOIN SYS.COLUMNS SC2 ON
                1=1
                AND SC2.object_id = ST.object_id
                AND SC2.column_id = SIC2.column_id
        where
            1=1
            AND SIC2.object_id = SI.object_id
            AND SIC2.index_id = SI.index_id
            AND SIC2.is_included_column = 0
    ) CD
    outer apply (
        select
            STRING_AGG(CONCAT(
                '[',
                SC2.NAME,
                '] '
            ),',') WITHIN GROUP ( ORDER BY sic2.KEY_ORDINAL, sic2.COLUMN_ID) COLUMNS
        from 
            sys.index_columns sic2
            INNER JOIN SYS.COLUMNS SC2 ON
                1=1
                AND SC2.object_id = ST.object_id
                AND SC2.column_id = SIC2.column_id
        where
            1=1
            AND SIC2.object_id = SI.object_id
            AND SIC2.index_id = SI.index_id
            AND SIC2.is_included_column = 1
    ) CD2
WHERE
    1=1
    AND ST.NAME = '<<table_name>>'
    AND SI.IS_PRIMARY_KEY = 1

-- NonPrimary Key
SELECT
    -- DISTINCT
    CONCAT(
        'if exists ',
        '( ',
            'select * from sys.indexes where name =''',SI.NAME,''' ',
        ') ',
        'BEGIN ',
            'DROP INDEX ',ST.NAME,'.',SI.NAME,' ',
        'END; ',
        ' ',
        'if NOT exists ',
        '( ',
            'select * from sys.indexes where name =''',SI.NAME,''' ',
        ') ',
        'BEGIN ',
            'CREATE ',
            CASE WHEN SI.is_unique = 1 THEN 'UNIQUE ' END,
            SI.TYPE_DESC COLLATE SQL_Latin1_General_CP1_CI_AS, 
            ' INDEX [',
            SI.NAME,
            '] ON [',
            sch.name,
            '].[',
            st.name,
            ']',
            CHAR(10),char(13),
            '(',
                CD.COLUMNS,
            ')',
            CASE WHEN LEN(CD2.COLUMNS) <> 0
                THEN
                    CONCAT(
                        'INCLUDE(',
                        CD2.COLUMNS,
                        ')'
                    )
                END,
            ' WITH (',
                LEFT(SI2.PARAMS,LEN(SI2.PARAMS)-1),
            ') ',
        'END;'
    ) CREATE_STATEMENT,
    1 LastColumn
FROM SYS.indexes SI
    outer apply(
        select
            CONCAT(
                CASE WHEN SI.is_padded = 1 THEN 'PAD_INDEX = ON, ' ELSE 'PAD_INDEX = OFF,' END,
                'STATISTICS_NORECOMPUTE = OFF, ',
                'SORT_IN_TEMPDB = OFF, ',
                'ONLINE = OFF, ',
                CASE WHEN SI.allow_row_locks = 1 THEN 'ALLOW_ROW_LOCKS = ON, ' ELSE 'ALLOW_ROW_LOCKS = OFF, 'END,
                CASE WHEN SI.allow_page_locks = 1 THEN 'ALLOW_PAGE_LOCKS = ON, ' ELSE 'ALLOW_PAGE_LOCKS = OFF, ' END,
                CASE WHEN SI.fill_factor <> 0 THEN CONCAT('FILLFACTOR = ', SI.fill_factor,', ') END,
                CASE WHEN SI.OPTIMIZE_FOR_SEQUENTIAL_KEY <> 0 THEN 'OPTIMIZE_FOR_SEQUENTIAL_KEY = ON, ' ELSE 'OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF, ' END
            ) PARAMS
        FROM
            SYS.INDEXES SI2
        WHERE
            1=1
            AND SI2.OBJECT_ID = SI.OBJECT_ID
            AND SI2.INDEX_ID = SI.INDEX_ID
    ) SI2
    INNER JOIN SYS.TABLES ST ON
        1=1
        and ST.object_id = SI.object_id
    inner join sys.schemas sch on
        1=1
        and sch.schema_id = st.schema_id
    -- Columns in index
    outer apply (
        select
            STRING_AGG(CONCAT(
                '[',
                SC2.NAME,
                '] ',
                case when sic2.is_descending_key  = 1 then 'DESC' else 'ASC' end
            ),',') WITHIN GROUP ( ORDER BY sic2.KEY_ORDINAL, sic2.COLUMN_ID) COLUMNS
        from 
            sys.index_columns sic2
            INNER JOIN SYS.COLUMNS SC2 ON
                1=1
                AND SC2.object_id = ST.object_id
                AND SC2.column_id = SIC2.column_id
        where
            1=1
            AND SIC2.object_id = SI.object_id
            AND SIC2.index_id = SI.index_id
            AND SIC2.is_included_column = 0
    ) CD
    outer apply (
        select
            STRING_AGG(CONCAT(
                '[',
                SC2.NAME,
                '] '
            ),',') WITHIN GROUP ( ORDER BY sic2.KEY_ORDINAL, sic2.COLUMN_ID) COLUMNS
        from 
            sys.index_columns sic2
            INNER JOIN SYS.COLUMNS SC2 ON
                1=1
                AND SC2.object_id = ST.object_id
                AND SC2.column_id = SIC2.column_id
        where
            1=1
            AND SIC2.object_id = SI.object_id
            AND SIC2.index_id = SI.index_id
            AND SIC2.is_included_column = 1 
    ) CD2
WHERE
    1=1
    AND ST.NAME = '<<table_name>>'
    AND SI.IS_PRIMARY_KEY = 0

Upvotes: 1

mr R

Reputation: 1126

Check my solution here: https://stackoverflow.com/a/55742250/1831734

Output

Create                                                                                                      Drop                                    Rebuild
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
CREATE CLUSTERED INDEX [PK_Table1] ON [Table1] ( [Tab1_ID] )                                                DROP INDEX [PK_Table1] ON [Table1]      ALTER INDEX [PK_Table1] ON [Table1] REBUILD 
CREATE UNIQUE INDEX [IX_Table1_Name] ON [Table1] ( [Tab1_Name] )                                            DROP INDEX [IX_Table1_Name] ON [Table1] ALTER INDEX [IX_Table1_Name] ON [Table1] REBUILD 
CREATE NONCLUSTERED INDEX [IX_Table2] ON [Table2] ( [Tab2_Name], [Tab2_City] )  INCLUDE ( [Tab2_PhoneNo] )  DROP INDEX [IX_Table2] ON [Table2]      ALTER INDEX [IX_Table2] ON [Table2] REBUILD

Upvotes: 4

David Adlington

Reputation: 666

You can do it on a table by table basis by using the "Object Explorer" window

Go to the Indexes folder in Management studio, highlight the folder then open the Object Explorer pane

You can then "shift Select" all of the indexes on that table, if you right click to script "CREATE TO" it will create a script with all the relevant indexes for you.

Upvotes: 1

David Brabant

Reputation: 43499

I wrote something for that a while ago. You might have to modify it for your needs, but at least you have a skeleton.

if exists (select 1 from information_schema.routines where routine_name = 'Script_CreateIndex')
    drop proc Script_CreateIndex
go

create proc Script_CreateIndex (
    @TableName varchar(124)
)
as
begin
    if not exists (select 1 from sys.indexes where object_name(object_id) = @TableName and type_desc in ('CLUSTERED', 'NONCLUSTERED'))
        return

    declare @IndexList table (
        Id int identity,
        IndexName varchar(124),
        IndexDescription varchar(max),
        IndexKeys varchar(max)
    )

    insert @IndexList(IndexName, IndexDescription, IndexKeys)
        exec sp_helpindex @TableName

    if (select count(*) from @IndexList) > 0
    begin
        select '-- Creating indexes for table ' + @TableName

        while exists (select 1 from @IndexList) 
        begin
            declare @Id int, @IndexName varchar(124), @IndexDescription varchar(max), @IndexKeys varchar(max)
            select top 1 @Id = Id, @IndexName = IndexName, @IndexDescription = IndexDescription, @IndexKeys = IndexKeys from @IndexList order by Id
            delete from @IndexList where Id = @Id

            declare @Clustered varchar(10), @Unique varchar(7)

            select @Clustered = case when patindex('%nonclustered%', @IndexDescription) > 0 then '' else ' clustered ' end
            select @Unique = case when patindex('%unique%', @IndexDescription) > 0 then ' unique ' else '' end

            select 'if not exists (select 1 from sys.indexes where name = ''' + @IndexName + ''')'
            select 'begin'
            select char(9) + 'create' + @Unique + @Clustered + ' index [' + @IndexName + '] on [' + @TableName + '](' + @IndexKeys + ')'
            select char(9) + 'select ''Index ' + @IndexName + ' created.'''
            select 'end'
            select 'go'
        end

        select ''
        select ''
    end
end
go

grant exec on Script_CreateIndex to public
select 'Script_CreateIndex compiled.' 'Job'
go

Upvotes: 4

podiluska

Reputation: 51494

Use Generate Scripts from SQL Management Studio and choose the "Script Indexes" options (under Advanced Scripting options)

Upvotes: 13

Generate CREATE INDEX statements in SQL Server

Answers (5)

Related Questions