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

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

上一篇:Windows下搭建:Telegraf+Influxdb+Grafana


下一篇:C# 泛型