--1. get all indexes from current db, place in temp table
select
tablename = schemas.name + '].[' + object_name(i.object_id),
tableid = i.object_id,
indexid = i.index_id,
indexname = i.name,
isunique = indexproperty (i.object_id,i.name,'isunique'),
isclustered = indexproperty (i.object_id,i.name,'isclustered'),
indexfillfactor = indexproperty (i.object_id,i.name,'indexfillfactor'),
CASE WHEN i.filter_definition IS NULL THEN '' ELSE ' WHERE '+i.filter_definition END Filter_Definition
into #tmp_indexes
from sys.indexes i
JOIN sys.objects ON i.object_id = objects.object_id 
JOIN sys.schemas ON objects.schema_id = schemas.schema_id 
where i.index_id > 0 and i.index_id < 255 --not certain about this
--AND INDEXPROPERTY (i.object_id,i.name,'ISUNIQUE') = 0 --comment out to include unique and
AND INDEXPROPERTY (i.object_id,i.name,'ISCLUSTERED') =0 --include PK's

--add additional columns to store include and key column lists
alter table #tmp_indexes add keycolumns varchar(4000), includes varchar(4000)
go
--################################################################################################


--2. loop through tables, put include and index columns into variables
declare @isql_key varchar(4000), @isql_incl varchar(4000), @tableid int, @indexid int

declare index_cursor cursor for
select tableid, indexid from #tmp_indexes 

open index_cursor
fetch next from index_cursor into @tableid, @indexid

while @@fetch_status <> -1
begin

select @isql_key = '', @isql_incl = ''

select --i.name, sc.colid, sc.name, ic.index_id, ic.object_id, *
--key column
@isql_key = case ic.is_included_column 
when 0 then 
case ic.is_descending_key 
when 1 then @isql_key + coalesce(sc.name,'') + ' DESC, '
else @isql_key + coalesce(sc.name,'') + ' ASC, '
end
else @isql_key end,

--include column
@isql_incl = case ic.is_included_column 
when 1 then 
case ic.is_descending_key 
when 1 then @isql_incl + coalesce(sc.name,'') + ', ' 
else @isql_incl + coalesce(sc.name,'') + ', ' 
end
else @isql_incl end
from sysindexes i
INNER JOIN sys.index_columns AS ic ON (ic.column_id > 0 and (ic.key_ordinal > 0 or ic.partition_ordinal = 0 or ic.is_included_column != 0)) AND (ic.index_id=CAST(i.indid AS int) AND ic.object_id=i.id)
INNER JOIN sys.columns AS sc ON sc.object_id = ic.object_id and sc.column_id = ic.column_id

where i.indid > 0 and i.indid < 255
and (i.status & 64) = 0
and i.id = @tableid and i.indid = @indexid
order by i.name, case ic.is_included_column when 1 then ic.index_column_id else ic.key_ordinal end


if len(@isql_key) > 1 set @isql_key = left(@isql_key, len(@isql_key) -1)
if len(@isql_incl) > 1 set @isql_incl = left(@isql_incl, len(@isql_incl) -1)

update #tmp_indexes 
set keycolumns = @isql_key,
includes = @isql_incl
where tableid = @tableid and indexid = @indexid

fetch next from index_cursor into @tableid,@indexid
end

close index_cursor
deallocate index_cursor

--remove invalid indexes,ie ones without key columns
delete from #tmp_indexes where keycolumns = ''
--################################################################################################




--3. output the index creation scripts
set nocount on

--separator
select '---------------------------------------------------------------------'

--create index scripts (for backup)
SELECT 
'CREATE ' 
+ CASE WHEN ISUNIQUE = 1 THEN 'UNIQUE ' ELSE '' END 
+ CASE WHEN ISCLUSTERED = 1 THEN 'CLUSTERED ' ELSE '' END 
+ 'INDEX [' + INDEXNAME + ']' 
+' ON [' + TABLENAME + '] '
+ '(' + keycolumns + ')' 
+ CASE 
WHEN INDEXFILLFACTOR = 0 AND ISCLUSTERED = 1 AND INCLUDES = '' THEN Filter_Definition 
WHEN INDEXFILLFACTOR = 0 AND ISCLUSTERED = 0 AND INCLUDES = '' THEN Filter_Definition + ' WITH (ONLINE = ON)' 
WHEN INDEXFILLFACTOR <> 0 AND ISCLUSTERED = 0 AND INCLUDES = '' THEN Filter_Definition + ' WITH (ONLINE = ON, FILLFACTOR = ' + CONVERT(VARCHAR(10),INDEXFILLFACTOR) + ')'
WHEN INDEXFILLFACTOR = 0 AND ISCLUSTERED = 0 AND INCLUDES <> '' THEN ' INCLUDE (' + INCLUDES + ') '+Filter_Definition+' WITH (ONLINE = ON)'
ELSE ' INCLUDE(' + INCLUDES + ') '+Filter_Definition+' WITH (FILLFACTOR = ' + CONVERT(VARCHAR(10),INDEXFILLFACTOR) + ', ONLINE = ON)' 
END
FROM #tmp_indexes
where left(tablename,3) not in ('sys', 'dt_') --exclude system tables
order by tablename, indexid, indexname 



--makes the drop
SELECT 'DROP INDEX ' 
+' [' + tablename + '].['
+ indexname + ']'
FROM #tmp_indexes where left(tablename,3) not in ('sys', 'dt_') 

--Drop the temp table again
DROP TABLE #tmp_indexes

set nocount off

--Script SQL 2008+ Indexes AS CREATE INDEX Statements
--http://www.sqlservercentral.com/Forums/Topic401795-566-1.aspx
--original version submitted by anonymous/unknown contributor
--enhanced and contributed to discussion by Lowell
--enhanced by thorv-918308 to correct index columns in the correct sequence
--enhanced by thorv-918308 added fill factor, and with ONLINE = ON 
--enhanced by mfuller333 to include DROP statements!
--enhanced by Sander A. to include filtered indexes!
--enhanced by craigborri to put the clustered indexes first, you'll want to do if you ever run this for real
/*if you get this error:
    Msg 207, Level 16, State 1, Line 22
    Invalid column name 'filter_definition'.
    Msg 207, Level 16, State 1, Line 24
    Invalid column name 'filter_definition'.
    Msg 208, Level 16, State 0, Line 11
    Invalid object name '#tmp_indexes'.
    
    the issue is the database in question is SQL 2005: filter_definition is SQL 2008
    see the inline code to comment/uncomment to make this SQL 2005 compliant.
*/
--################################################################################################
--1. get all indexes from current db, place in temp table
--################################################################################################
SELECT
  tablename       = QUOTENAME(scmz.name) + '.' + QUOTENAME((OBJECT_NAME(ixz.object_id))),
  tableid         = ixz.object_id,
  indexid         = ixz.index_id,
  indexname       = ixz.name,
  isunique        = INDEXPROPERTY (ixz.object_id,ixz.name,'isunique'),
  isclustered     = INDEXPROPERTY (ixz.object_id,ixz.name,'isclustered'),
  indexfillfactor = INDEXPROPERTY (ixz.object_id,ixz.name,'indexfillfactor'),
  --SQL2008+ Filtered indexes:
  CASE 
    WHEN ixz.filter_definition IS NULL 
    THEN '' 
    ELSE ' WHERE ' + ixz.filter_definition 
  END Filter_Definition
  --For 2005, which did not have filtered indexes, comment out the above CASE statement, and uncomment this:
  --'' AS Filter_Definition
INTO #tmp_indexes
FROM sys.indexes ixz
  INNER JOIN sys.objects obz
    ON ixz.object_id = obz.object_id 
  INNER JOIN sys.schemas scmz
    ON obz.schema_id = scmz.schema_id 
WHERE ixz.index_id > 0 
  AND ixz.index_id < 255 ---- 0 = HEAP index, 255 = TEXT columns index
 AND INDEXPROPERTY (ixz.object_id,ixz.name,'ISUNIQUE')    = 0 -- comment out to include unique and
 AND INDEXPROPERTY (ixz.object_id,ixz.name,'ISCLUSTERED') = 0 -- comment out to include  PK's

--add additional columns to store include and key column lists
ALTER TABLE #tmp_indexes ADD keycolumns VARCHAR(4000), includes VARCHAR(4000)
GO
--################################################################################################
--2. loop through tables, put include and index columns into variables
--################################################################################################
DECLARE @isql_key  VARCHAR(4000), 
        @isql_incl VARCHAR(4000),
        @tableid   INT, 
        @indexid   INT

DECLARE index_cursor CURSOR 
  FOR
    SELECT 
      tableid, 
      indexid 
    FROM #tmp_indexes 

--################################################################################################
--Cursor Block
--################################################################################################
OPEN index_cursor
FETCH NEXT FROM index_cursor INTO @tableid, @indexid

WHILE @@FETCH_STATUS <> -1
  BEGIN
    SELECT @isql_key = '', @isql_incl = ''
    SELECT --ixz.name, colz.colid, colz.name, ixcolz.index_id, ixcolz.object_id, *
      --key column
      @isql_key = CASE ixcolz.is_included_column 
                    WHEN 0 
                    THEN CASE ixcolz.is_descending_key 
                           WHEN 1 
                           THEN @isql_key + COALESCE(colz.name,'') + ' DESC, '
                           ELSE @isql_key + COALESCE(colz.name,'') + ' ASC, '
                         END
                    ELSE @isql_key 
                  END,

      --include column
      @isql_incl = CASE ixcolz.is_included_column 
                     WHEN 1 
                     THEN CASE ixcolz.is_descending_key 
                            WHEN 1 
                            THEN @isql_incl + COALESCE(colz.name,'') + ', ' 
                            ELSE @isql_incl + COALESCE(colz.name,'') + ', ' 
                          END
                     ELSE @isql_incl 
                   END
    FROM sysindexes ixz
      INNER JOIN sys.index_columns AS ixcolz 
        ON (ixcolz.column_id > 0 
        AND (    ixcolz.key_ordinal > 0 
             OR  ixcolz.partition_ordinal = 0 
             OR  ixcolz.is_included_column != 0)
             ) 
        AND (    ixcolz.index_id=CAST(ixz.indid AS INT) 
             AND ixcolz.object_id=ixz.id
             )
      INNER JOIN sys.columns AS colz 
        ON  colz.object_id = ixcolz.object_id 
        AND colz.column_id = ixcolz.column_id
    WHERE ixz.indid > 0 AND ixz.indid < 255
      AND (ixz.status & 64) = 0
      AND ixz.id            = @tableid 
      AND ixz.indid         = @indexid
    ORDER BY 
      ixz.name, 
      CASE ixcolz.is_included_column 
        WHEN 1 
        THEN ixcolz.index_column_id 
        ELSE ixcolz.key_ordinal 
      END

    --remove any trailing commas from the cursor results
    IF LEN(@isql_key)  > 1 SET @isql_key  = LEFT(@isql_key,  LEN(@isql_key)  -1)
    IF LEN(@isql_incl) > 1 SET @isql_incl = LEFT(@isql_incl, LEN(@isql_incl) -1)
    --put the columns collection into our temp table
    UPDATE #tmp_indexes 
    SET keycolumns = @isql_key,
        includes   = @isql_incl
    WHERE tableid  = @tableid 
      AND indexid  = @indexid
    FETCH NEXT FROM index_cursor INTO @tableid,@indexid
  END --WHILE
--################################################################################################
--End Cursor Block
--################################################################################################
CLOSE index_cursor
DEALLOCATE index_cursor

--remove invalid indexes, ie ones without key columns
DELETE FROM #tmp_indexes WHERE keycolumns = ''
--################################################################################################
--3. output the index creation scripts
--################################################################################################
SET NOCOUNT ON
--separator for results-to-text
SELECT '---------------------------------------------------------------------'
--create index scripts (for backup)
SELECT 'IF NOT EXISTS(SELECT OBJECT_ID(' + INDEXNAME + ')' + ' ' + 
  'CREATE ' 
  + CASE WHEN ISUNIQUE    = 1 THEN 'UNIQUE '    ELSE '' END 
  + CASE WHEN ISCLUSTERED = 1 THEN 'CLUSTERED ' ELSE '' END 
  + 'INDEX ' + QUOTENAME(INDEXNAME) 
  + ' ON '    + (TABLENAME) + ' '
  + '(' + keycolumns + ')' 
  + CASE 
      WHEN INDEXFILLFACTOR = 0  AND ISCLUSTERED = 1 AND INCLUDES = ''  THEN Filter_Definition 
      WHEN INDEXFILLFACTOR = 0  AND ISCLUSTERED = 0 AND INCLUDES = ''  THEN Filter_Definition + ' WITH (ONLINE = ON)' 
      WHEN INDEXFILLFACTOR <> 0 AND ISCLUSTERED = 0 AND INCLUDES = ''  THEN Filter_Definition + ' WITH (ONLINE = ON, FILLFACTOR = ' + CONVERT(VARCHAR(10),INDEXFILLFACTOR) + ')'
      WHEN INDEXFILLFACTOR = 0  AND ISCLUSTERED = 0 AND INCLUDES <> '' THEN ' INCLUDE (' + INCLUDES + ') ' + Filter_Definition + ' WITH (ONLINE = ON)'
      ELSE ' INCLUDE(' + INCLUDES + ') ' + Filter_Definition + ' WITH (FILLFACTOR = ' + CONVERT(VARCHAR(10),INDEXFILLFACTOR) + ', ONLINE = ON)' 
    END
FROM #tmp_indexes
WHERE LEFT(tablename,3) NOT IN ('sys', 'dt_') --exclude system tables
ORDER BY 
ISCLUSTERED desc, 
tablename, 
indexid, 
indexname
--makes the drop
SELECT 
  'DROP INDEX ' 
  + ' ' + (tablename) + '.'
  + (indexname) + ''
FROM #tmp_indexes 
WHERE LEFT(tablename,4) NOT IN ('[sys', 'dt_') 

--Drop the temp table again
DROP TABLE #tmp_indexes

SET NOCOUNT OFF

IF NOT EXISTS(SELECT OBJECT_ID(IX_GMATDET_HELP) 
  CREATE INDEX [IX_GMATDET_HELP] ON [dbo].[GMATDET] (
ACTTRANSDETTBLKEY ASC, 
SOURCETBLKEY ASC, 
YEARTBLKEY ASC, 
SETASDTBLKEY ASC, 
SUBGRANTTBLKEY ASC, 
TRANSACTIONAMT ASC, 
RECEIPT1AMT ASC, 
RECEIPT2AMT ASC, 
RECEIPT3AMT ASC, 
ADJUSTMENT ASC) WITH (ONLINE = ON)
--your script is generating the same indexname for each column in the definition,
-- instead of one index featuring all columns.
CREATE NONCLUSTERED INDEX [IX_GMATDET_HELP] ON GMATDET( [ACTTRANSDETTBLKEY] ASC) WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]
CREATE NONCLUSTERED INDEX [IX_GMATDET_HELP] ON GMATDET( [SOURCETBLKEY] ASC) WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]
CREATE NONCLUSTERED INDEX [IX_GMATDET_HELP] ON GMATDET( [YEARTBLKEY] ASC) WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]
CREATE NONCLUSTERED INDEX [IX_GMATDET_HELP] ON GMATDET( [SETASDTBLKEY] ASC) WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]
CREATE NONCLUSTERED INDEX [IX_GMATDET_HELP] ON GMATDET( [SUBGRANTTBLKEY] ASC) WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]
CREATE NONCLUSTERED INDEX [IX_GMATDET_HELP] ON GMATDET( [TRANSACTIONAMT] ASC) WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]
CREATE NONCLUSTERED INDEX [IX_GMATDET_HELP] ON GMATDET( [RECEIPT1AMT] ASC) WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]
CREATE NONCLUSTERED INDEX [IX_GMATDET_HELP] ON GMATDET( [RECEIPT2AMT] ASC) WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]
CREATE NONCLUSTERED INDEX [IX_GMATDET_HELP] ON GMATDET( [RECEIPT3AMT] ASC) WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]
CREATE NONCLUSTERED INDEX [IX_GMATDET_HELP] ON GMATDET( [ADJUSTMENT] ASC) WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]