Generate Commands to Create/Drop Recommended Missing Indexes

DROP PROCEDURE dbo.usp_GenerateCreateDropMissingIndexCommands

GO

CREATE PROCEDURE dbo.usp_GenerateCreateDropMissingIndexCommands @AutoGenLabel VARCHAR(20) = ''

AS

/***************************************************************

Purpose: To generate commands to create and drop missing Indexes

recommended by the DBMS.

This utilises the dbo.udf_GetTopNMissingIndexes() function

to retrieve a list of 20 and builds both the CREATE and

DROP commands

The parameter @AutoGenLabel will be used in the index name along with

date and index handle to help identify/group autogen indexes)

No changes are made by this stored procedure

The recommendations generated should be reviewed before running the code

Author: ChillyDBA

History: 12 Jul 2012

****************************************************************/

;WITH MissingIndexes AS

(

SELECT TOP 20

DatabaseName,

TableName,

EqualityColumns,

InequalityColumns,

IncludedColumns,

UserSeeks,

UserScans,

LastUserSeek,

AvgUserImpact,

AvgTotalUserCost,

WeightedCost,

IndexHandle

FROM dbo.udf_GetTopNMissingIndexes(20)

)

SELECT

'CREATE NONCLUSTERED INDEX '

+ QUOTENAME('IX_' + @AutoGenLabel + '_Autogen_'

+ REPLACE(REPLACE(CONVERT(VARCHAR(25), GETDATE(), 113), ' ', '_'), ':', '_')

+ '_' + CAST(IndexHandle AS VARCHAR(22))

)

+ ' ON ' + DatabaseName + '.' + TableName + '('

+ CASE

WHEN EqualityColumns IS NULL THEN InequalityColumns

WHEN InequalityColumns IS NULL THEN EqualityColumns

ELSE EqualityColumns + ',' + InequalityColumns

END

+ ')'

+ CASE

WHEN IncludedColumns IS NOT NULL THEN

' INCLUDE ( ' + IncludedColumns + ')'

ELSE ''

END AS MissingIndexSQL

FROM MissingIndexes

UNION

SELECT

'DROP INDEX '

+ TableName

+ '.'

+ ('IX_' + @AutoGenLabel + '_Autogen_'

+ REPLACE(REPLACE(CONVERT(VARCHAR(25), GETDATE(), 113), ' ', '_'), ':', '_')

+ '_' + CAST(IndexHandle AS VARCHAR(22))

)

FROM MissingIndexes