CreateIndexScripts

/* Script to create all index scripts into a temp table */

IF OBJECT_ID('tempdb..#CreateIndexes') IS NOT NULL DROP TABLE #CreateIndexes
GO

declare @SchemaName varchar(100)
declare @TableName varchar(256)
declare @IndexName varchar(256)
declare @ColumnName varchar(100)
declare @is_unique varchar(100)
declare @IndexTypeDesc varchar(100)
declare @FileGroupName varchar(100)
declare @is_disabled bit
declare @is_primary_key bit
declare @IndexOptions varchar(max)
declare @IndexColumnId int
declare @IsDescendingKey int 
declare @IsIncludedColumn int
declare @TSQLScripCreationIndex varchar(max)
declare @TSQLScripDisableIndex varchar(max)

CREATE TABLE #CreateIndexes (
                            SchemaName varchar(max)
                            ,TableName varchar(max)
                            ,IndexName varchar(max)
                            ,is_unique varchar(max)
                            ,IndexTypeDesc varchar(max)
                            ,is_disabled bit
                            ,is_primary_key bit
                            ,FileGroupName varchar(max)
                            ,DropScript varchar(max)
                            ,TSQLScripCreationIndex varchar(max)
                            ,TSQLScripDisableIndex varchar(max)
                            )

declare CursorIndex cursor for
 select schema_name(t.schema_id) [schema_name]
 , t.name
 , ix.name
 , case when ix.is_unique = 1 then 'UNIQUE ' else '' END 
 , ix.type_desc
 , case when ix.is_padded=1 then 'PAD_INDEX = ON, ' else 'PAD_INDEX = OFF, ' end
    + case when ix.allow_page_locks=1 then 'ALLOW_PAGE_LOCKS = ON, ' else 'ALLOW_PAGE_LOCKS = OFF, ' end
    + case when ix.allow_row_locks=1 then  'ALLOW_ROW_LOCKS = ON, ' else 'ALLOW_ROW_LOCKS = OFF, ' end
    + case when INDEXPROPERTY(t.object_id, ix.name, 'IsStatistics') = 1 then 'STATISTICS_NORECOMPUTE = ON, ' else 'STATISTICS_NORECOMPUTE = OFF, ' end
    + case when ix.ignore_dup_key=1 then 'IGNORE_DUP_KEY = ON, ' else 'IGNORE_DUP_KEY = OFF, ' end
    + 'SORT_IN_TEMPDB = OFF, FILLFACTOR =' + CAST(ix.fill_factor AS VARCHAR(3)) AS IndexOptions
 
 , ix.is_disabled 
 , ix.is_primary_key
 , FILEGROUP_NAME(ix.data_space_id) FileGroupName
 from sys.tables t 
 inner join sys.indexes ix on t.object_id=ix.object_id
 where ix.type>0 AND t.is_ms_shipped=0 AND t.name<>'sysdiagrams' --and t.name = 'Entity'
 order by schema_name(t.schema_id), t.name, ix.name

open CursorIndex
fetch next from CursorIndex into  @SchemaName, @TableName, @IndexName, @is_unique, @IndexTypeDesc, @IndexOptions,@is_disabled, @is_primary_key, @FileGroupName

while (@@fetch_status=0)
begin
 declare @IndexColumns varchar(max)
 declare @IncludedColumns varchar(max)
 
 set @IndexColumns=''
 set @IncludedColumns=''
 
 declare CursorIndexColumn cursor for 
  select col.name, ixc.is_descending_key, ixc.is_included_column
  from sys.tables tb 
  inner join sys.indexes ix on tb.object_id=ix.object_id
  inner join sys.index_columns ixc on ix.object_id=ixc.object_id and ix.index_id= ixc.index_id
  inner join sys.columns col on ixc.object_id =col.object_id  and ixc.column_id=col.column_id
  where ix.type>0 and (ix.is_primary_key=0 or ix.is_unique_constraint=0)
  and schema_name(tb.schema_id)=@SchemaName and tb.name=@TableName and ix.name=@IndexName
  order by ixc.index_column_id
 
 open CursorIndexColumn 
 fetch next from CursorIndexColumn into  @ColumnName, @IsDescendingKey, @IsIncludedColumn
 
 while (@@fetch_status=0)
 begin
  if @IsIncludedColumn=0 
   set @IndexColumns=@IndexColumns + @ColumnName  + case when @IsDescendingKey=1  then ' DESC, ' else  ' ASC, ' end
  else 
   set @IncludedColumns=@IncludedColumns  + @ColumnName  +', ' 

  fetch next from CursorIndexColumn into @ColumnName, @IsDescendingKey, @IsIncludedColumn
 end

 close CursorIndexColumn
 deallocate CursorIndexColumn

 set @IndexColumns = substring(@IndexColumns, 1, len(@IndexColumns)-1)
 set @IncludedColumns = case when len(@IncludedColumns) >0 then substring(@IncludedColumns, 1, len(@IncludedColumns)-1) else '' end
 --  print @IndexColumns
 --  print @IncludedColumns

 set @TSQLScripCreationIndex =''
 set @TSQLScripDisableIndex =''
 set @TSQLScripCreationIndex=
 CASE    WHEN @is_primary_key = 1 THEN 'ALTER TABLE ' + QUOTENAME(@SchemaName) +'.'+ QUOTENAME(@TableName) + ' ADD CONSTRAINT ' + QUOTENAME(@IndexName) + ' PRIMARY KEY CLUSTERED (' + @IndexColumns + ' ) WITH ' + @IndexOptions + ' ON ' + QUOTENAME(@FileGroupName) + ';'  
         ELSE 'CREATE '+ @is_unique  +@IndexTypeDesc + ' INDEX ' +QUOTENAME(@IndexName)+' ON ' + QUOTENAME(@SchemaName) +'.'+ QUOTENAME(@TableName)+ '('+@IndexColumns+') '
 + CASE WHEN len(@IncludedColumns)>0 THEN 'INCLUDE (' + @IncludedColumns+ ')' else '' end + CHAR(13)+'WITH (' + @IndexOptions+ ') ON ' + QUOTENAME(@FileGroupName) + ';'  
  END

 --if @is_disabled=1 
  set  @TSQLScripDisableIndex=  CASE WHEN @IndexTypeDesc = 'CLUSTERED' THEN 'ALTER TABLE ' + QUOTENAME(@SchemaName) +'.'+ QUOTENAME(@TableName) + ' DROP CONSTRAINT ' + QUOTENAME(@IndexName)
                                        ELSE 'DROP INDEX  ' + QUOTENAME(@IndexName) + ' ON ' + QUOTENAME(@SchemaName) +'.'+ QUOTENAME(@TableName) + CHAR(13) END

 print @TSQLScripCreationIndex
 print @TSQLScripDisableIndex

 INSERT INTO #CreateIndexes (SchemaName, TableName, IndexName, is_unique, IndexTypeDesc, is_disabled, is_primary_key ,FileGroupName, TSQLScripCreationIndex, TSQLScripDisableIndex)
 SELECT @SchemaName, @TableName, @IndexName, @is_unique, @IndexTypeDesc, @is_disabled, @is_primary_key, @FileGroupName, @TSQLScripCreationIndex, @TSQLScripDisableIndex

 fetch next from CursorIndex into  @SchemaName, @TableName, @IndexName, @is_unique, @IndexTypeDesc, @IndexOptions,@is_disabled, @is_primary_key ,@FileGroupName

end
close CursorIndex
deallocate CursorIndex

SELECT * FROM #CreateIndexes ORDER BY TableName, IndexName