Generate CREATE INDEX statements in SQL Server
Use Generate Scripts from SQL Management Studio and choose the "Script Indexes" options (under Advanced Scripting options)
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
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