Sql缺失索引查询，自动创建执行语句_sql server 索引缺失并创建-优快云博客

文章详细描述了如何在SQL中创建一个视图来检测数据库中的缺失索引，并提供了创建函数和存储过程，用于生成针对特定数据库的创建索引和删除现有索引的SQL语句，以优化查询性能。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

试图查询确实的索引


    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]'