Generate CREATE INDEX statements in SQL Server

Question

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.

Answer 1

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

Answer 2

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

Answer 3

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.