--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_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)
--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 + ']'
END
FROM #tmp_indexes ti
JOIN sys.indexes i ON ti.Object_id = i.object_id and ti.indexname = i.name
JOIN sys.filegroups fg on i.data_space_id = fg.data_space_id
WHERE LEFT(ti.tablename,3) NOT IN ('sys', 'dt_') --exclude system tables
ORDER BY
ti.tablename,
ti.indexid,
ti.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