SQLServerCentral / Article Discussions / Discuss content posted by Lowell / Article Discussions by Author / Script all indexes as CREATE INDEX statements / Latest Posts

RE: Script all indexes as CREATE INDEX statements

Mohammed Imran Ali — Tue, 22 May 2012 21:07:31 GMT

That was really helpfull. I was trying to execute on SQL Server 2008 R2 and bumped into an issue with the Collation parameters with the "[b]keycolumns[/b] ". To work around, I added [b]COLLATE DATABASE_DEFAULT[/b] for [b]keycolumns[/b] in the sections that generates the CREATE INDEX scripts.

RE: Script all indexes as CREATE INDEX statements

Lowell — Wed, 14 Mar 2012 06:30:18 GMT

Paul take a look at this post here in the Discussions thread;[b][url]http://www.sqlservercentral.com/Forums/FindPost1079779.aspx[/url][/b]That post ( a few posts above this) has a much more modern scripting style that correctly takes into consideration the column order on the index.the original script was contributed when SQL 2000 was the target database version...it's come a long way since then

RE: Script all indexes as CREATE INDEX statements

Paul Heiner — Tue, 13 Mar 2012 16:49:08 GMT

Code fixIt's already been commented on once, but the code has not been modified --ORDER BY SYSCOLUMNS.COLID - This is the original code -- it will create the composite indexes with the order of columns WRONG Tested on SQL 2000 SQL 2008 ORDER BY SYSINDEXKEYS.keyno - It should be Still a great script, saved me time, another example of test - test - test before you run a downloaded script.

RE: Script all indexes as CREATE INDEX statements

Lowell — Tue, 24 May 2011 09:20:14 GMT

[quote][b]patelekta (5/24/2011)[/b][hr]May be you will get your answer on http://ektaraval.blogspot.com/2010/07/query-to-generate-create-index.html[/quote]ekta your example only works if an index has one and only one column in it's definition...otherwise it scripts two definitions for the same index, but with different columns.for example, compare the results if a single index from the above script to yours:You'll want to enhance your version, maybe use the FOR XML to append all the columns together for the definition to really make yours valid for all indexes.[code]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][/code]

RE: Script all indexes as CREATE INDEX statements

patelekta — Tue, 24 May 2011 08:57:51 GMT

May be you will get your answer on http://ektaraval.blogspot.com/2010/07/query-to-generate-create-index.html

RE: Script all indexes as CREATE INDEX statements

SQLRNNR — Fri, 22 Apr 2011 12:11:03 GMT

Wow this script has really come a long way - even better than the last time I looked. I'm gonna need to compare to a different script I have with similar functionality now.

RE: Script all indexes as CREATE INDEX statements

craigborri — Fri, 22 Apr 2011 12:07:08 GMT

I'd suggest changing the order by on the last select to order by ISCLUSTERED desc, tablename, indexid, indexnameThis will insure the clustered indexes are created first, which you'll want to do if you ever run this for real.

RE: Script all indexes as CREATE INDEX statements

upretyd — Tue, 12 Apr 2011 08:15:11 GMT

--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 a tiny bit to include filegroups. Does not script partitioned indexes for now./*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 ixz.object_id, 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_DefinitionINTO #tmp_indexesFROM 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 listsALTER 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 INTDECLARE index_cursor CURSOR FOR SELECT tableid, indexid FROM #tmp_indexes --################################################################################################--Cursor Block--################################################################################################OPEN index_cursorFETCH NEXT FROM index_cursor INTO @tableid, @indexidWHILE @@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_cursorDEALLOCATE index_cursor--remove invalid indexes, ie ones without key columnsDELETE FROM #tmp_indexes WHERE keycolumns = ''--################################################################################################--3. output the index creation scripts--################################################################################################SET NOCOUNT ON--separator for results-to-textSELECT '---------------------------------------------------------------------'--create index scripts (for backup)--IF NOT EXISTS (SELECT * FROM dbo.sysindexes WHERE id = OBJECT_ID(N' + '''' +-- '[dbo].[' + tablename + ']' + '''' +--') AND name = N' + '''' +-- indexname + '''' + ')' +SELECT 'IF NOT EXISTS(SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N''' + ti.TABLENAME + '''' + ') AND name = N' + '''' + ti.INDEXNAME + '''' + ')' + ' ' + 'CREATE ' + CASE WHEN ti.ISUNIQUE = 1 THEN 'UNIQUE ' ELSE '' END + CASE WHEN ti.ISCLUSTERED = 1 THEN 'CLUSTERED ' ELSE '' END + 'INDEX ' + QUOTENAME(ti.INDEXNAME) + ' ON ' + (ti.TABLENAME) + ' ' + '(' + ti.keycolumns + ')' + CASE WHEN ti.INDEXFILLFACTOR = 0 AND ti.ISCLUSTERED = 1 AND INCLUDES = '' THEN ti.Filter_Definition + ' WITH (SORT_IN_TEMPDB = ON) ON [' + fg.name + ']' WHEN INDEXFILLFACTOR = 0 AND ti.ISCLUSTERED = 0 AND ti.INCLUDES = '' THEN ti.Filter_Definition + ' WITH (ONLINE = ON, SORT_IN_TEMPDB = ON) ON [' + fg.name + ']' WHEN INDEXFILLFACTOR <> 0 AND ti.ISCLUSTERED = 0 AND ti.INCLUDES = '' THEN ti.Filter_Definition + ' WITH (ONLINE = ON, SORT_IN_TEMPDB = ON, FILLFACTOR = ' + CONVERT(VARCHAR(10),ti.INDEXFILLFACTOR) + ') ON [' + fg.name + ']' WHEN INDEXFILLFACTOR = 0 AND ti.ISCLUSTERED = 0 AND ti.INCLUDES <> '' THEN ' INCLUDE (' + ti.INCLUDES + ') ' + ti.Filter_Definition + ' WITH (ONLINE = ON, SORT_IN_TEMPDB = ON) ON [' + fg.name + ']' ELSE ' INCLUDE(' + ti.INCLUDES + ') ' + ti.Filter_Definition + ' WITH (FILLFACTOR = ' + CONVERT(VARCHAR(10),ti.INDEXFILLFACTOR) + ', ONLINE = ON, SORT_IN_TEMPDB = ON) ON [' + fg.name + ']' ENDFROM #tmp_indexes tiJOIN sys.indexes i ON ti.Object_id = i.object_id and ti.indexname = i.nameJOIN sys.filegroups fg on i.data_space_id = fg.data_space_idWHERE LEFT(ti.tablename,3) NOT IN ('sys', 'dt_') --exclude system tablesORDER BY ti.tablename, ti.indexid, ti.indexname --makes the dropSELECT 'DROP INDEX ' + ' ' + (tablename) + '.' + (indexname) + ''FROM #tmp_indexes WHERE LEFT(tablename,4) NOT IN ('[sys', 'dt_') ----Drop the temp table againDROP TABLE #tmp_indexes--SET NOCOUNT OFF

RE: Script all indexes as CREATE INDEX statements

Lowell — Thu, 17 Mar 2011 09:17:45 GMT

wow this script has come a long way since the original version; thanks again to everyone who has contributed;I cleaned up the formatting a little bit and added some comments, so that folks run this against SQL 2005 don't panic when it fails due to the new filtered indexes;[code]--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_DefinitionINTO #tmp_indexesFROM 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 listsALTER 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 INTDECLARE index_cursor CURSOR FOR SELECT tableid, indexid FROM #tmp_indexes --################################################################################################--Cursor Block--################################################################################################OPEN index_cursorFETCH NEXT FROM index_cursor INTO @tableid, @indexidWHILE @@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_cursorDEALLOCATE index_cursor--remove invalid indexes, ie ones without key columnsDELETE FROM #tmp_indexes WHERE keycolumns = ''--################################################################################################--3. output the index creation scripts--################################################################################################SET NOCOUNT ON--separator for results-to-textSELECT '---------------------------------------------------------------------'--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)' ENDFROM #tmp_indexesWHERE LEFT(tablename,3) NOT IN ('sys', 'dt_') --exclude system tablesORDER BY ISCLUSTERED desc, tablename, indexid, indexname--makes the dropSELECT 'DROP INDEX ' + ' ' + (tablename) + '.' + (indexname) + ''FROM #tmp_indexes WHERE LEFT(tablename,4) NOT IN ('[sys', 'dt_') --Drop the temp table againDROP TABLE #tmp_indexesSET NOCOUNT OFF[/code]

RE: Script all indexes as CREATE INDEX statements

Sander A. — Thu, 13 Jan 2011 03:36:49 GMT

I made a little change to the last script to also include the where clause of a filtered index. I also removed the collate because this didn't work for me.[code]--1. get all indexes from current db, place in temp tableselecttablename = 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_Definitioninto #tmp_indexesfrom sys.indexes iJOIN 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 andAND INDEXPROPERTY (i.object_id,i.name,'ISCLUSTERED') =0 --include PK's--add additional columns to store include and key column listsalter table #tmp_indexes add keycolumns varchar(4000), includes varchar(4000)go--################################################################################################--2. loop through tables, put include and index columns into variablesdeclare @isql_key varchar(4000), @isql_incl varchar(4000), @tableid int, @indexid intdeclare index_cursor cursor forselect tableid, indexid from #tmp_indexes open index_cursorfetch next from index_cursor into @tableid, @indexidwhile @@fetch_status <> -1beginselect @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, 'endelse @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,'') + ', ' endelse @isql_incl endfrom sysindexes iINNER 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_idwhere i.indid > 0 and i.indid < 255and (i.status & 64) = 0and i.id = @tableid and i.indid = @indexidorder by i.name, case ic.is_included_column when 1 then ic.index_column_id else ic.key_ordinal endif 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_inclwhere tableid = @tableid and indexid = @indexidfetch next from index_cursor into @tableid,@indexidendclose index_cursordeallocate index_cursor--remove invalid indexes,ie ones without key columnsdelete from #tmp_indexes where keycolumns = ''--################################################################################################--3. output the index creation scriptsset nocount on--separatorselect '---------------------------------------------------------------------'--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)' ENDFROM #tmp_indexeswhere left(tablename,3) not in ('sys', 'dt_') --exclude system tablesorder by tablename, indexid, indexname --makes the dropSELECT 'DROP INDEX ' +' [' + tablename + '].['+ indexname + ']'FROM #tmp_indexes where left(tablename,3) not in ('sys', 'dt_') --Drop the temp table againDROP TABLE #tmp_indexesset nocount off[/code]

RE: Script all indexes as CREATE INDEX statements

mfuller333 — Fri, 06 Aug 2010 10:46:36 GMT

Nice script...Added back the ability to include or exclude PK and Unique indexes, as well as schema if your not using the default dbo. Also now creates the drop statement as well.--1. get all indexes from current db, place in temp tableselect tablename = schemas.name + '].[' + object_name(i.id), tableid = i.id, indexid = i.indid, indexname = i.name, i.status, isunique = indexproperty (i.id,i.name,'isunique'), isclustered = indexproperty (i.id,i.name,'isclustered'), indexfillfactor = indexproperty (i.id,i.name,'indexfillfactor')into #tmp_indexesfrom sysindexes iJOIN sys.objects ON i.id = objects.object_id JOIN sys.schemas ON objects.schema_id = schemas.schema_id where i.indid > 0 and i.indid < 255 --not certain about thisand (i.status & 64) = 0 AND INDEXPROPERTY (i.id,i.name,'ISUNIQUE') =0 --comment out to include unique and AND INDEXPROPERTY (i.id,i.name,'ISCLUSTERED') =0 --include PK's--add additional columns to store include and key column listsalter table #tmp_indexes add keycolumns varchar(4000) COLLATE Latin1_General_BIN2, includes varchar(4000)COLLATE Latin1_General_BIN2go--################################################################################################--2. loop through tables, put include and index columns into variablesdeclare @isql_key varchar(4000), @isql_incl varchar(4000), @tableid int, @indexid intdeclare index_cursor cursor forselect tableid, indexid from #tmp_indexes open index_cursorfetch next from index_cursor into @tableid, @indexidwhile @@fetch_status <> -1begin 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 endclose index_cursordeallocate index_cursor--remove invalid indexes,ie ones without key columnsdelete from #tmp_indexes where keycolumns = ''--################################################################################################--3. output the index creation scriptsset nocount on--separatorselect '---------------------------------------------------------------------'--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 '' WHEN INDEXFILLFACTOR = 0 AND ISCLUSTERED = 0 AND INCLUDES = '' THEN ' WITH (ONLINE = ON)' WHEN INDEXFILLFACTOR <> 0 AND ISCLUSTERED = 0 AND INCLUDES = '' THEN ' WITH (ONLINE = ON, FILLFACTOR = ' + CONVERT(VARCHAR(10),INDEXFILLFACTOR) + ')' WHEN INDEXFILLFACTOR = 0 AND ISCLUSTERED = 0 AND INCLUDES <> '' THEN ' INCLUDE (' + INCLUDES + ') WITH (ONLINE = ON)' ELSE ' INCLUDE(' + INCLUDES + ') WITH (FILLFACTOR = ' + CONVERT(VARCHAR(10),INDEXFILLFACTOR) + ', ONLINE = ON)' ENDFROM #tmp_indexeswhere left(tablename,3)COLLATE Latin1_General_BIN2 not in ('sys', 'dt_') --exclude system tablesorder by tablename, indexid, indexname COLLATE Latin1_General_BIN2set nocount off--drop table #tmp_indexes--makes the drop SELECT 'DROP INDEX ' +' [' + tablename + '].[' + indexname + ']' FROM #tmp_indexes where left(tablename,3)COLLATE Latin1_General_BIN2 not in ('sys', 'dt_')

RE: Script all indexes as CREATE INDEX statements

SQLRNNR — Tue, 09 Mar 2010 16:36:41 GMT

Thanks Lowell and Thorv.

RE: Script all indexes as CREATE INDEX statements

alaine.warfield — Tue, 09 Mar 2010 16:22:49 GMT

Thank you very much for posting these scripts.I had scripts for SQL 2000 but nothing that handled include columnsThis is really appreciated!

RE: Script all indexes as CREATE INDEX statements

thorv-918308 — Fri, 05 Jun 2009 05:27:15 GMT

Ah additionally I have added fill factor, and a really cool addition- with ONLINE = ON for nonclustered indexes (so you don't lock the production table when adding the index).[b]Note:[/b] ONLINE = ON doesn't work for clustered indexes.

RE: Script all indexes as CREATE INDEX statements

thorv-918308 — Fri, 05 Jun 2009 05:23:50 GMT

HiThanks for the original query!I modified it to have includes.Additionally will create composite index columns in the correct sequence, I found that your original query worked well, however the sequence that composite indexes added the columns in was incorrect.e.g. existing index would look like this ->CREATE INDEX ix_123 on [TABLE] ([COL1], [COL2], [COL3][)however the output would look like this ->CREATE INDEX ix_123 on [TABLE] ([b][COL2], [COL1], [COL3][/b])[code]--1. get all indexes from current db, place in temp tableselect tablename = object_name(i.id), tableid = i.id, indexid = i.indid, indexname = i.name, i.status, isunique = indexproperty (i.id,i.name,'isunique'), isclustered = indexproperty (i.id,i.name,'isclustered'), indexfillfactor = indexproperty (i.id,i.name,'indexfillfactor')into #tmp_indexesfrom sysindexes iwhere i.indid > 0 and i.indid < 255 --not certain about thisand (i.status & 64) = 0 --existing indexes--add additional columns to store include and key column listsalter table #tmp_indexes add keycolumns varchar(4000), includes varchar(4000)go--################################################################################################--2. loop through tables, put include and index columns into variablesdeclare @isql_key varchar(4000), @isql_incl varchar(4000), @tableid int, @indexid intdeclare index_cursor cursor forselect tableid, indexid from #tmp_indexes open index_cursorfetch next from index_cursor into @tableid, @indexidwhile @@fetch_status <> -1begin 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 endclose index_cursordeallocate index_cursor--remove invalid indexes,ie ones without key columnsdelete from #tmp_indexes where keycolumns = ''--################################################################################################--3. output the index creation scriptsset nocount on--separatorselect '---------------------------------------------------------------------'--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 '' WHEN INDEXFILLFACTOR = 0 AND ISCLUSTERED = 0 AND INCLUDES = '' THEN ' WITH (ONLINE = ON)' WHEN INDEXFILLFACTOR <> 0 AND ISCLUSTERED = 0 AND INCLUDES = '' THEN ' WITH (ONLINE = ON, FILLFACTOR = ' + CONVERT(VARCHAR(10),INDEXFILLFACTOR) + ')' WHEN INDEXFILLFACTOR = 0 AND ISCLUSTERED = 0 AND INCLUDES <> '' THEN ' INCLUDE (' + INCLUDES + ') WITH (ONLINE = ON)' ELSE ' INCLUDE(' + INCLUDES + ') WITH (FILLFACTOR = ' + CONVERT(VARCHAR(10),INDEXFILLFACTOR) + ', ONLINE = ON)' ENDFROM #tmp_indexeswhere left(tablename,3) not in ('sys', 'dt_') --exclude system tablesorder by tablename, indexid, indexnameset nocount off--drop table #tmp_indexes[/code]

RE: Script all indexes as CREATE INDEX statements

Lowell — Sun, 31 May 2009 19:57:09 GMT

i contributed that script a while ago, and clearly it's only for SQL 2000;here's someone elses script that I saved in my snippets that does INCLUDE columns;it does PRINT statements instead of a SELECT, but that's easy to modify; it alos includes the owner/schema, where my original was just assuming dbo. I'll modify my script to do the same thing and post it after i test it a little bit.[code]--------------------------------------------------------------------declare@object_id int,@index_id tinyint,@schema_name sysname,@table_name sysname,@index_name sysname,@type tinyint,@uniqueness bit,@indexed_column sysname,@included_column sysname,@indexed_columns varchar(max),@included_columns varchar(max),@has_included_cols bit,@is_descending_key bit,@stmt varchar(max),@crlf char(2)set @crlf = char(13) + char(10)declare indexes cursorforselect schema_name = s.name, table_name = t.name, index_id = i.index_id, index_name = i.name, type = i.type, uniqueness = i.is_uniquefrom sys.schemas s join sys.tables t on s.schema_id = t.schema_id join sys.indexes i on t.object_id = i.object_idwhere i.type > 0 -- none -heaporder by s.name, t.name, i.index_idopen indexesfetch indexesinto @schema_name, @table_name , @index_id , @index_name , @type , @uniqueness while @@fetch_status<>(-1)begin select @object_id = object_id(@schema_name + '.' + @table_name) set @indexed_columns = '(' declare indexed_columns cursor for select c.name, ic.is_descending_key from sys.index_columns ic join sys.columns c on ic.column_id = c.column_id and ic.object_id = c.object_id where ic.object_id = @object_id and ic.index_id = @index_id and ic.is_included_column = 0 order by ic.index_column_id open indexed_columns fetch indexed_columns into @indexed_column, @is_descending_key while @@fetch_status<>(-1) begin set @indexed_columns = @indexed_columns + @indexed_column + case @is_descending_key when 1 then ' desc ' else '' end + ', ' fetch indexed_columns into @indexed_column, @is_descending_key end close indexed_columns deallocate indexed_columns set @indexed_columns = left(@indexed_columns, len(@indexed_columns)-1) + ')' if exists (select object_id from sys.index_columns where object_id = @object_id and index_id = @index_id and is_included_column = 1 ) begin set @included_columns = 'include (' declare included_columns cursor for select c.name, ic.is_descending_key from sys.index_columns ic join sys.columns c on ic.column_id = c.column_id and ic.object_id = c.object_id where ic.object_id = @object_id and ic.index_id = @index_id and ic.is_included_column = 1 order by ic.index_column_id open included_columns fetch included_columns into @included_column, @is_descending_key while @@fetch_status<>(-1) begin set @included_columns = @included_columns + @included_column + case @is_descending_key when 1 then ' desc ' else '' end + ', ' fetch included_columns into @included_column, @is_descending_key end close included_columns deallocate included_columns set @included_columns = left(@included_columns, len(@included_columns)-1) + ')' + @crlf end set @stmt = 'create ' + case @uniqueness when 1 then 'unique ' else '' end + case @type when 1 then 'clustered ' else '' end + 'index ' + @index_name + @crlf + 'on ' + @schema_name + '.' + @table_name + @indexed_columns + @crlf + isnull(@included_columns,'') + 'g' + 'o' + @crlf + @crlf print @stmt fetch indexes into @schema_name, @table_name , @index_id , @index_name , @type , @uniqueness endclose indexesdeallocate indexes[/code]

RE: Script all indexes as CREATE INDEX statements

anatshapira8 — Sun, 31 May 2009 07:04:48 GMT

ORDER BY SYSCOLUMNS.COLID should be ORDER BY sysindexkeys.keynoOrdering by COlID causes wrong columns order in the key.

RE: Script all indexes as CREATE INDEX statements

DBA-640728 — Wed, 13 May 2009 15:59:55 GMT

did you find a way to get the included columns?

RE: Script all indexes as CREATE INDEX statements

Pravin Patil — Tue, 30 Sep 2008 23:17:15 GMT

Res Sir,Thanks for this script.I have i problem that it will not run for sql server 2005 databases having compatibility level 90.Kindly request you to send the script which will run on sql server 2005 databases having compatibility level 90.Thanking you in advance.

RE: Script all indexes as CREATE INDEX statements

Vishal Sinha — Thu, 05 Jun 2008 14:39:32 GMT

this is a great script. But it does not work if there are Included columns. Example my index looks like this:CREATE UNIQUE INDEX IDX_indexName ON dbo.TableName ( Column1 , Column2 )INCLUDE ( Column3 , Column4 , Column5 )But your script creates it as follows:CREATE UNIQUE INDEX IDX_indexName ON dbo.TableName ( Column1 , Column2 , Column3 , Column4 , Column5 )Any clues on how to modify your script to get what I need?Thanks!

Script all indexes as CREATE INDEX statements

Lowell — Sun, 23 Sep 2007 19:53:05 GMT

Comments posted to this topic are about the item [B]Script all indexes as CREATE INDEX statements[/B]