SQLServer批量生成某数据库中的所有索引的创建脚本

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 
                        

                            

                                
< 上一篇
MySQL ------  相关概念简介（DBMS ，MySQL连接）（十八）
                            
                            

                                 
下一篇 >
docker部署springboot前后端分离项目（jdk+jar包+mysql+redis+nginx）