SQLServer批量生成某数据库中的所有索引的创建脚本
看到几 个方法，在此记录一下：
方法一 （可生成索引）：（未验证可行性 转自：https://blog.csdn.net/happyflystone/article/details/4538254 ）
/* exec sp_autoIdx ‘1’ ----直接生成索引 or exec sp_autoIdx ‘0’ @ifexec决定是否直接exec(@sqlon)在当前数据库生成索引。 */ CREATE PROC sp_autoIdx @ifexec CHAR(1) AS BEGIN DECLARE @TB NVARCHAR(40); DECLARE @SQLON VARCHAR(200) , @SQLINCLUDE VARCHAR(200); DECLARE CUR CURSOR FOR SELECT DISTINCT statement FROM sys.dm_db_missing_index_details AS MID CROSS APPLY sys.dm_db_missing_index_columns(MID.index_handle) INNER JOIN sys.dm_db_missing_index_groups AS MIG ON MIG.index_handle = MID.index_handle ORDER BY statement ASC; OPEN CUR; FETCH CUR INTO @TB; WHILE ( @@FETCH_STATUS = 0 ) BEGIN PRINT '--' + @TB + ':'; SET @SQLON = 'CREATE INDEX IDX_' + UPPER(PARSENAME(@TB, 1)) + '_' + REPLACE(CAST(NEWID() AS VARCHAR(60)), '-', '_') + ' ON ' + @TB + '('; SET @SQLINCLUDE = ' INCLDE('; WITH T AS ( SELECT mig.* , statement AS table_name , column_id , column_name , column_usage , rowid = ROW_NUMBER() OVER ( PARTITION BY index_group_handle, statement ORDER BY index_group_handle, CASE column_usage WHEN 'EQUALITY' THEN 1 WHEN 'INEQUALITY' THEN 2 ELSE 3 END ) , levelid = RANK() OVER ( PARTITION BY index_group_handle, statement ORDER BY index_group_handle ) FROM sys.dm_db_missing_index_details AS mid CROSS APPLY sys.dm_db_missing_index_columns(mid.index_handle) INNER JOIN sys.dm_db_missing_index_groups AS mig ON mig.index_handle = mid.index_handle ) SELECT @SQLON = @SQLON + CASE WHEN column_usage IN ( 'EQUALITY', 'INEQUALITY' ) THEN column_name + ',' ELSE '' END , @SQLINCLUDE = @SQLINCLUDE + CASE WHEN column_usage = 'INCLUDE' THEN column_name + ',' ELSE '' END FROM T WHERE table_name = @TB AND levelid = 1; IF RIGHT(@SQLON, 1) = '(' PRINT '根据动态管理对象无法智能生成索引计划'; ELSE BEGIN SET @SQLON = LEFT(@SQLON, LEN(@SQLON) - 1) + ')'; IF RIGHT(@SQLINCLUDE, 1) = '(' PRINT @SQLON; IF @ifexec = '1' EXEC(@SQLON); ELSE BEGIN SET @SQLINCLUDE = LEFT(@SQLINCLUDE, LEN(@SQLINCLUDE) - 1) + ')'; SET @SQLON = @SQLON + @SQLINCLUDE; PRINT @SQLON; IF @ifexec = '1' EXEC(@SQLON); END; END; FETCH CUR INTO @TB; END; CLOSE CUR; DEALLOCATE CUR; END; GO
方法二：（未验证可行性 转自： https://blog.csdn.net/iteye_18688/article/details/81717229）
/*
调用方法： exec p_helpindex 'tb_test'
-----drop proc p_helpindex
*/
CREATE PROC p_helpindex @tbname sysname = '' , @CLUSTERED INT = '1' AS --生成索引信息及索引创建脚本（Sql Server 2000） IF @tbname IS NULL OR @tbname = '' RETURN -1; DECLARE @t TABLE ( table_name NVARCHAR(100) , schema_name NVARCHAR(100) , fill_factor INT , is_padded INT , ix_name NVARCHAR(100) , type INT , keyno INT , column_name NVARCHAR(200) , cluster VARCHAR(20) , ignore_dupkey VARCHAR(20) , [unique] VARCHAR(20) , groupfile VARCHAR(10) ); DECLARE @table_name NVARCHAR(100) , @schema_name NVARCHAR(100) , @fill_factor INT , @is_padded INT , @ix_name NVARCHAR(100) , @ix_name_old NVARCHAR(100) , @type INT , @keyno INT , @column_name NVARCHAR(100) ,--@column_name_temp nvarchar(500), @cluster VARCHAR(20) , @ignore_dupkey VARCHAR(20) , @unique VARCHAR(20) , @groupfile VARCHAR(10); DECLARE ms_crs_ind CURSOR LOCAL STATIC FOR SELECT DISTINCT table_name = a.name , schema_name = b.name , fill_factor = c.OrigFillFactor , is_padded = CASE WHEN c.status = 256 THEN 1 ELSE 0 END , ix_name = c.name , type = c.indid , d.keyno , column_name = e.name + CASE WHEN INDEXKEY_PROPERTY(a.id, c.indid, d.keyno, 'isdescending') = 1 THEN ' desc ' ELSE '' END , CASE WHEN ( c.status & 16 ) <> 0 THEN 'clustered' ELSE 'nonclustered' END , CASE WHEN ( c.status & 1 ) <> 0 THEN 'IGNORE_DUP_KEY' ELSE '' END , CASE WHEN ( c.status & 2 ) <> 0 THEN 'unique' ELSE '' END , g.groupname FROM sysobjects a INNER JOIN sysusers b ON a.uid = b.uid INNER JOIN sysindexes c ON a.id = c.id INNER JOIN sysindexkeys d ON a.id = d.id AND c.indid = d.indid INNER JOIN syscolumns e ON a.id = e.id AND d.colid = e.colid INNER JOIN sysfilegroups g ON g.groupid = c.groupid LEFT JOIN master.dbo.spt_values f ON f.number = c.status AND f.type = 'I' WHERE a.id = OBJECT_ID(@tbname) AND c.indid < 255 AND ( c.status & 64 ) = 0 AND c.indid >= @CLUSTERED ORDER BY c.indid , d.keyno; OPEN ms_crs_ind; FETCH ms_crs_ind INTO @table_name, @schema_name, @fill_factor, @is_padded, @ix_name, @type, @keyno, @column_name, @cluster, @ignore_dupkey, @unique, @groupfile; IF @@fetch_status < 0 BEGIN DEALLOCATE ms_crs_ind; RAISERROR(15472,-1,-1); --'Object does not have any indexes.'--无效索引（即没有键列的索引） RETURN -1; END; WHILE @@fetch_status >= 0 BEGIN IF EXISTS ( SELECT 1 FROM @t WHERE ix_name = @ix_name ) UPDATE @t SET column_name = column_name + ',' + @column_name WHERE ix_name = @ix_name; ELSE INSERT INTO @t SELECT @table_name , @schema_name , @fill_factor , @is_padded , @ix_name , @type , @keyno , @column_name , @cluster , @ignore_dupkey , @unique , @groupfile; FETCH ms_crs_ind INTO @table_name, @schema_name, @fill_factor, @is_padded, @ix_name, @type, @keyno, @column_name, @cluster, @ignore_dupkey, @unique, @groupfile; END; DEALLOCATE ms_crs_ind; SELECT 'CREATE ' + UPPER([unique]) + CASE WHEN [unique] = '' THEN '' ELSE ' ' END + UPPER(cluster) + ' INDEX ' + ix_name + ' ON ' + table_name + '(' + column_name + ')' + CASE WHEN fill_factor > 0 OR is_padded = 1 OR ( UPPER(cluster) != 'NONCLUSTERED' AND ignore_dupkey = 'IGNORE_DUP_KEY' ) THEN ' WITH ' + CASE WHEN is_padded = 1 THEN 'PAD_INDEX,' ELSE '' END + CASE WHEN fill_factor > 0 THEN 'FILLFACTOR =' + LTRIM(fill_factor) ELSE '' END + CASE WHEN ignore_dupkey = 'IGNORE_DUP_KEY' AND UPPER(cluster) = 'NONCLUSTERED' THEN CASE WHEN ( fill_factor > 0 OR is_padded = 1 ) THEN ',IGNORE_DUP_KEY' ELSE ',IGNORE_DUP_KEY' END ELSE '' END ELSE '' END + ' ON [' + groupfile + ']' AS col FROM @t; RETURN 0; GO
方法三：（未验证可行性 转自：https://www.cnblogs.com/yy3b2007com/p/4541405.html）
--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 --2。遍历表，将include和index列放入变量中 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://blog.csdn.net/happyflystone
