缺失索引自动创建语句

  -- CREATE FUNCTION fn_Index_CreateIndexName
    CREATE FUNCTION [dbo].[fn_Index_CreateIndexName] (@equality_columns NVARCHAR(4000), _
    @Inequality_columns NVARCHAR(4000), @index_handlE INT) RETURNS VARCHAR(128)
    AS
    BEGIN
            DECLAR
    E @IndexName NVARCHAR(MAX)
        SET @IndexName = ISNULL(@equali
    ty_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
    -- CREATE FUNCTION vw_Index_MissingIndex
    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 mid
    INNER JOIN sys.databases d
    on d.database_id = mid.database_id
    GO
    CREATE PROCEDURE [dbo].[usp_Index_MissingIndexCreationStatements]
    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
    GO