Sql缺失索引查询，自动创建执行语句

 试图查询确实的索引

 
    CREATE VIEW [dbo].[vw_Index_MissingIndex] 
    AS
    SELECT  '[' + d.name + ']' as DBName,[dbo].[fn_Index_CreateIndexName](mid.equality_columns,mid.Inequality_columns,mid.index_handlE) AS ID,
            REPLACE(mid.equality_columns,',',' ASC,') AS equality_columns,
            REPLACE(mid.Inequality_columns,',',' ASC,') AS Inequality_columns,
            mid.Included_columns,
            mid.[statement]
    FROM sys.dm_db_missing_index_details as mid
    INNER JOIN sys.databases d
    on d.database_id = mid.database_id
 
    GO

给索引命名 

CREATE FUNCTION [dbo].[fn_Index_CreateIndexName] (@equality_columns NVARCHAR(4000), @Inequality_columns NVARCHAR(max), @index_handlE INT) RETURNS VARCHAR(max)
    AS
    BEGIN
 
        declare @IndexName NVARCHAR(MAX)
        SET @IndexName = ISNULL(@equality_columns,@Inequality_columns)
		SET @IndexName = LTRIM(REPLACE(@IndexName,'[','_'))
		SET @IndexName = RTRIM(REPLACE(@IndexName,']','_'))
		SET @IndexName = REPLACE(@IndexName,',','')
		SET @IndexName = REPLACE(@IndexName,'_ _','_')
 
        IF LEN(@IndexName) > 120
        BEGIN
            SET @IndexName = SUBSTRING(@IndexName,0,120)
        END  
        SET @IndexName = @IndexName + CAST(@index_handlE AS NVARCHAR(15))
        RETURN @IndexName 
    END
 
    GO

创建索引语句,按查询表方式返回

/*注意@DBNAME 为[数据库名字]*/ 
create PROCEDURE [dbo].[usp_Index_MissingIndexCreationStatements](@DBNAME VARCHAR(100))
    AS
 
    DECLARE @IndexCreationPlaceholder_Start  AS NVARCHAR(MAX)
    DECLARE @IndexCreationPlaceholder_End  AS NVARCHAR(MAX)
 
    -- PREPARE PLACEHOLDER
 
    SET @IndexCreationPlaceholder_Start = 'IF NOT EXISTS (SELECT * FROM {2}.sys.indexes WHERE [name] = ''IX_{0}'' )    
                    
                    BEGIN
                    CREATE NONCLUSTERED INDEX [IX_{0}] ON {1}';
 
    SET @IndexCreationPlaceholder_End = ' WITH (PAD_INDEX = OFF,STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF,ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
     ON [PRIMARY]
                    END;' + char(13) + char(10)
 
    -- STATEMENT CREATION
 
    SELECT DBName,CASE
        WHEN NOT mid.equality_columns IS NULL AND NOT mid.Inequality_columns IS NULL THEN
                    REPLACE(REPLACE(REPLACE(@IndexCreationPlaceholder_Start,'{0}', mid.ID),
                    '{1}',mid.[statement]),'{2}',mid.DBName)
                    + '( ' + COALESCE(mid.equality_columns,'') + ' ASC,' + 
                       COALESCE(mid.Inequality_columns,'') + ' ASC )' +
                    COALESCE('INCLUDE ( ' + mid.Included_columns + ' ) ','')
                    + @IndexCreationPlaceholder_End
 
            WHEN mid.equality_columns IS NULL AND NOT mid.Inequality_columns IS NULL THEN
                    REPLACE(REPLACE(REPLACE(@IndexCreationPlaceholder_Start,'{0}', mid.ID),'{1}',mid.[statement]),'{2}',mid.DBName)
                + ' ( ' + COALESCE(mid.Inequality_columns,'') +  ' ASC ) ' +
                COALESCE('INCLUDE ( ' + mid.Included_columns + ' ) ','')
                + @IndexCreationPlaceholder_End
 
        WHEN NOT mid.equality_columns IS NULL AND mid.Inequality_columns IS NULL THEN
                REPLACE(REPLACE(REPLACE(@IndexCreationPlaceholder_Start,'{0}', mid.ID),'{1}',mid.[statement]),'{2}',mid.DBName)
                + ' ( ' + COALESCE(mid.equality_columns,'') +  ' ASC) '
                 +COALESCE('INCLUDE ( ' + mid.Included_columns + ' ) ','')
                + @IndexCreationPlaceholder_End
        ELSE NULL
    END AS Index_Creation_Statement,
    ' DROP INDEX [IX_' + mid.ID  + '] ON ' + mid.[statement]   + char(13) + char(10) AS Index_Drop_Statement FROM [dbo].[vw_Index_MissingIndex] AS mid
    
   WHERE DBName = @DBNAME 
 
    GO
    

创建索引，直接打印为日志，复制日志直接执行

 create PROCEDURE [dbo].[usp_Index_MissingIndexCreationStatements_PRINT](@DBNAME VARCHAR(100))
    AS
 
    DECLARE @IndexCreationPlaceholder_Start  AS NVARCHAR(MAX)
    DECLARE @IndexCreationPlaceholder_End  AS NVARCHAR(MAX)
 
    -- PREPARE PLACEHOLDER
 
    SET @IndexCreationPlaceholder_Start = 'IF NOT EXISTS (SELECT * FROM {2}.sys.indexes WHERE [name] = ''IX_{0}'' )    
        BEGIN
			CREATE NONCLUSTERED INDEX [IX_{0}] ON {1} 
							';
 
    SET @IndexCreationPlaceholder_End = ' 
			WITH (PAD_INDEX = OFF,STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF,ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
			ON [PRIMARY]
       END;' + char(13) + char(10)
 
    -- STATEMENT CREATION
 
	
	
	declare @Index_Creation_Statement varchar(max)
	declare @Index_Drop_Statement varchar(max)
 
	
    SELECT CASE
        WHEN NOT mid.equality_columns IS NULL AND NOT mid.Inequality_columns IS NULL THEN
                    REPLACE(REPLACE(REPLACE(@IndexCreationPlaceholder_Start,'{0}', mid.ID),
                    '{1}',mid.[statement]),'{2}',mid.DBName)
                    + '( ' + COALESCE(mid.equality_columns,'') + ' ASC,
                    ' + 
                       COALESCE(mid.Inequality_columns,'') + ' ASC ) ' +  char(13) + char(10) + '
                        ' +
                    COALESCE('INCLUDE ( ' + mid.Included_columns + ' ) ','')
                    + @IndexCreationPlaceholder_End
 
            WHEN mid.equality_columns IS NULL AND NOT mid.Inequality_columns IS NULL THEN
                    REPLACE(REPLACE(REPLACE(@IndexCreationPlaceholder_Start,'{0}', mid.ID),'{1}',mid.[statement]),'{2}',mid.DBName)
                + ' ( ' + COALESCE(mid.Inequality_columns,'') +  ' ASC ) ' + 
                COALESCE('INCLUDE ( ' + mid.Included_columns + ' ) ','')
                + @IndexCreationPlaceholder_End
 
        WHEN NOT mid.equality_columns IS NULL AND mid.Inequality_columns IS NULL THEN
                REPLACE(REPLACE(REPLACE(@IndexCreationPlaceholder_Start,'{0}', mid.ID),'{1}',mid.[statement]),'{2}',mid.DBName)
                + ' ( ' + COALESCE(mid.equality_columns,'') +  ' ASC) '
                 +COALESCE('INCLUDE ( ' + mid.Included_columns + ' ) ','')
                + @IndexCreationPlaceholder_End
        ELSE NULL
    END AS Index_Creation_Statement,
    ' DROP INDEX [IX_' + mid.ID  + '] ON ' + mid.[statement]   + char(13) + char(10) AS Index_Drop_Statement INTO #TEST_INDEX_TMP FROM [dbo].[vw_Index_MissingIndex] AS mid
    
   WHERE DBName = @DBNAME
   
   
   DECLARE INDEX_CURSOR CURSOR FOR SELECT * FROM #TEST_INDEX_TMP
   
  
   
  
   OPEN INDEX_CURSOR
   fetch NEXT FROM INDEX_CURSOR into @Index_Creation_Statement,@Index_Drop_Statement
   
   
   print 'use' + @DBNAME
	print 'Go'
	
	
   while @@FETCH_STATUS  = 0 
   begin
	 print @Index_Creation_Statement
	 print 'Go'
	 fetch NEXT FROM INDEX_CURSOR into @Index_Creation_Statement,@Index_Drop_Statement;
   end
   close INDEX_CURSOR
   deallocate INDEX_CURSOR
 
 SELECT * FROM #TEST_INDEX_TMP
 TRUNCATE TABLE #TEST_INDEX_TMP;
   DROP TABLE #TEST_INDEX_TMP;
   
    GO
    

执行

exec [usp_Index_MissingIndexCreationStatements_PRINT] '[TEST]'