--1. get all indexes from current db, place in temp table select schemaName = s.name, 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_indexes from sysindexes i INNER JOIN sys.tables t ON i.id = t.object_id INNER JOIN sys.schemas s ON t.schema_id = s.schema_id where i.indid > 0 and i.indid < 255 --not certain about this and (i.status & 64) = 0 --existing indexes --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 [' + schemaName + '].[' + 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)' END FROM #tmp_indexes where left(tablename,3) not in ('sys', 'dt_') --exclude system tables order by schemaName, tablename, indexid, indexname set nocount off drop table #tmp_indexes
参考：
http://www.sqlservercentral.com/Forums/Topic796512-391-1.aspx