usp_IndexCreationStatements

IF EXISTS (SELECT name FROM dbo.sysobjects WHERE id = Object_id(N'[dbo].[usp_IndexCreation]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
DROP PROCEDURE [dbo].[usp_IndexCreation]
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE PROC usp_IndexCreation AS
--
-- usp_IndexCreation.sql - Index creation statements
-- SQL2005/2008 only
-- 2007-10-11 Pedro Lopes (NovaBase) pedro.lopes@novabase.pt
--
-- 2008-01-17 Verify redundant indexes before creation.
-- EXEC usp_IndexCreation
--
SET NOCOUNT ON

DECLARE @IC VARCHAR(4000), @ICWI VARCHAR(4000), @RI VARCHAR(4000), @DB VARCHAR(100), @IHK int, @TBL VARCHAR(100), @Qryins VARCHAR(4000)

CREATE TABLE #IndexCreation (
    [DBid] int,
    DBName VARCHAR(100),
    [Table] VARCHAR(100),
    [Equality] VARCHAR(1000),
    [Included] VARCHAR(4000),
    [Ix_Handle_Key] int,
    Col1 VARCHAR(100) NULL,
    Col2 VARCHAR(100) NULL,
    Col3 VARCHAR(100) NULL,
    Col4 VARCHAR(100) NULL,
    Col5 VARCHAR(100) NULL,
    Col6 VARCHAR(100) NULL,
    Col7 VARCHAR(100) NULL,
    Col8 VARCHAR(100) NULL,
    Col9 VARCHAR(100) NULL,
    Col10 VARCHAR(100) NULL,
    Col11 VARCHAR(100) NULL,
    Col12 VARCHAR(100) NULL,
    Col13 VARCHAR(100) NULL,
    Col14 VARCHAR(100) NULL,
    Col15 VARCHAR(100) NULL,
    Col16 VARCHAR(100) NULL
    )

CREATE TABLE #IndexCreationSec (
    [Ix_Handle_Key] int,
    DBName VARCHAR(100),
    [Table] VARCHAR(100),
    [Equality] VARCHAR(1000),
    )

CREATE TABLE #TempIndexCreation(
    DBName sysname,
    Ix_Handle_Key int,
    [Table] VARCHAR(50),
    Col1 VARCHAR(100) NULL,
    Col2 VARCHAR(100) NULL,
    Col3 VARCHAR(100) NULL,
    Col4 VARCHAR(100) NULL,
    Col5 VARCHAR(100) NULL,
    Col6 VARCHAR(100) NULL,
    Col7 VARCHAR(100) NULL,
    Col8 VARCHAR(100) NULL,
    Col9 VARCHAR(100) NULL,
    Col10 VARCHAR(100) NULL,
    Col11 VARCHAR(100) NULL,
    Col12 VARCHAR(100) NULL,
    Col13 VARCHAR(100) NULL,
    Col14 VARCHAR(100) NULL,
    Col15 VARCHAR(100) NULL,
    Col16 VARCHAR(100) NULL
    )

INSERT INTO #IndexCreation ([DBid],DBName,[Table],[Equality],[Included],[Ix_Handle_Key])
SELECT i.database_id AS [DBid], 
    m.[name] AS DBName, 
    i.[statement] AS [Table], 
    CASE    WHEN (i.equality_columns IS NOT NULL AND i.inequality_columns IS NULL) THEN i.equality_columns
            WHEN (i.equality_columns IS NULL AND i.inequality_columns IS NOT NULL) THEN i.inequality_columns
            ELSE i.equality_columns + ', ' + i.inequality_columns END AS [Equality], 
    i.included_columns AS [Included],
    i.index_handle [Ix_Handle_Key]
FROM sys.dm_db_missing_index_details i, master..sysdatabases m
WHERE i.database_id = m.dbid
ORDER BY database_id, Equality, i.index_handle

INSERT INTO #IndexCreationSec
SELECT MAX(DISTINCT [Ix_Handle_Key]) AS [Ix_Handle_Key], DBName, [Table], [Equality] FROM #IndexCreation 
GROUP BY DBName, [Table], [Equality]

SELECT DBName, COUNT(DISTINCT [Ix_Handle_Key]) AS Indexes_to_Create FROM #IndexCreationSec
GROUP BY DBName

SELECT DBName, COUNT(DISTINCT [Ix_Handle_Key]) AS Indexes_with_INCLUDEs_to_Create FROM #IndexCreation
WHERE [Included] IS NOT NULL
GROUP BY DBName

PRINT '############# Index creation statements #############' + CHAR(10)

DECLARE cIC CURSOR FOR
SELECT 'USE ' + QUOTENAME(DBName) + CHAR(10) + 'GO' + CHAR(10) + 'IF EXISTS (SELECT name FROM sys.indexes WHERE name = N''IX_' + 
    CAST([Ix_Handle_Key] AS NVARCHAR) + ''') DROP INDEX ' + [Table] + '.IX_' + 
    CAST([Ix_Handle_Key] AS NVARCHAR) + CHAR(10) + 'GO' + CHAR(10) + 'CREATE NONCLUSTERED INDEX IX_' + 
    CAST([Ix_Handle_Key] AS NVARCHAR) + ' ON ' + [Table] + ' (' + [Equality] + ') WITH PAD_INDEX, FILLFACTOR = 90' + CHAR(10) + 'GO' + CHAR(10) -- AS CreateStatement 
FROM #IndexCreationSec
ORDER BY DBName, [Table], [Ix_Handle_Key]
OPEN cIC 
FETCH NEXT FROM cIC INTO @IC
WHILE @@FETCH_STATUS = 0 
    BEGIN 
        PRINT @IC
        FETCH NEXT FROM cIC INTO @IC
    END
CLOSE cIC 
DEALLOCATE cIC

PRINT '############# Index creation statements with INCLUDEs #############' + CHAR(10)

DECLARE cICWI CURSOR FOR
SELECT 'USE ' + QUOTENAME(DBName) + CHAR(10) + 'GO' + CHAR(10) + 'IF EXISTS (SELECT name FROM sys.indexes WHERE name = N''IX_' + 
    CAST([Ix_Handle_Key] AS NVARCHAR) + ''') DROP INDEX ' + [Table] + '.IX_' + 
    CAST([Ix_Handle_Key] AS NVARCHAR) + CHAR(10) + 'GO' + CHAR(10) + 'CREATE NONCLUSTERED INDEX IX_' + 
    CAST([Ix_Handle_Key] AS NVARCHAR) + ' ON ' + [Table] + ' (' + [Equality] + ')' + CHAR(10) + 'INCLUDE (' + [Included] + ') WITH PAD_INDEX, FILLFACTOR = 90' + CHAR(10) + 'GO' + CHAR(10) -- AS CreateStatementWithInclude 
FROM #IndexCreation
WHERE [Included] IS NOT NULL
ORDER BY DBName, [Ix_Handle_Key]
OPEN cICWI 
FETCH NEXT FROM cICWI INTO @ICWI
WHILE @@FETCH_STATUS = 0 
    BEGIN 
        PRINT @ICWI
        FETCH NEXT FROM cICWI INTO @ICWI
    END
CLOSE cICWI 
DEALLOCATE cICWI


DECLARE cRI CURSOR FOR SELECT QUOTENAME(DBName), [Ix_Handle_Key], [Table], [Equality]
                        FROM #IndexCreation
                        --WHERE [Included] IS NOT NULL
                        GROUP BY DBName, [Table], [Equality], [Ix_Handle_Key]
                        ORDER BY DBName, [Ix_Handle_Key]
OPEN cRI 
FETCH NEXT FROM cRI INTO @DB, @IHK, @TBL, @RI
WHILE @@FETCH_STATUS = 0 
    BEGIN 
        DECLARE @tblCol VARCHAR(4000)
        DECLARE @Col VARCHAR(50), @Colctr int, @Colctrdesc int, @StartPos int, @Length int
        SET @Colctr = 0
        SET @Colctrdesc = 0
        WHILE LEN(@RI) > 0
            BEGIN
                SET @StartPos = CHARINDEX(',', @RI)
                IF @StartPos < 0 SET @StartPos = 0
                SET @Length = LEN(@RI) - @StartPos - 1
                IF @Length < 0 SET @Length = 0
                IF @StartPos > 0
                    BEGIN
                        SET @Col = SUBSTRING(@RI, 1, @StartPos - 1)
                        SET @RI = SUBSTRING(@RI, @StartPos + 1, LEN(@RI) - @StartPos)
                        SET @Colctr = @Colctr + 1
                    END
                ELSE
                    BEGIN
                        SET @Col = @RI
                        SET @RI = ''
                        SET @Colctr = @Colctr + 1
                    END
                IF @tblCol IS NULL
                    BEGIN SET @tblCol = CHAR(39) + LTRIM(RTRIM(@Col)) + CHAR(39) END
                ELSE
                    BEGIN SET @tblCol = @tblCol + ',' + CHAR(39) + LTRIM(RTRIM(@Col)) + CHAR(39) END
            END
        IF @Colctr < 16
            BEGIN
                SET @Colctrdesc = 16 - @Colctr
                WHILE @Colctrdesc > 0
                    BEGIN
                        SET @tblCol = @tblCol + ',' + CHAR(39) + CHAR(39)
                        SET @Colctrdesc = @Colctrdesc - 1
                    END
                SET @Colctr = 0
            END
        SET @Qryins = 'INSERT INTO #TempIndexCreation (DBName,Ix_Handle_Key,[Table],Col1,Col2,Col3,Col4,Col5,Col6,Col7,Col8,Col9,Col10,Col11,Col12,Col13,Col14,Col15,Col16) VALUES (''' + @DB + ''',' + RTRIM(@IHK) + ',''' + @TBL + ''',' + @tblCol + ')'
        EXECUTE (@Qryins)
        SET @tblCol = NULL
        FETCH NEXT FROM cRI INTO @DB, @IHK, @TBL, @RI
    END
CLOSE cRI 
DEALLOCATE cRI

UPDATE #IndexCreation
SET #IndexCreation.Col1 = #TempIndexCreation.Col1,#IndexCreation.Col2 = #TempIndexCreation.Col2,#IndexCreation.Col3 = #TempIndexCreation.Col3,#IndexCreation.Col4 = #TempIndexCreation.Col4,#IndexCreation.Col5 = #TempIndexCreation.Col5,
    #IndexCreation.Col6 = #TempIndexCreation.Col6,#IndexCreation.Col7 = #TempIndexCreation.Col7,#IndexCreation.Col8 = #TempIndexCreation.Col8,#IndexCreation.Col9 = #TempIndexCreation.Col9,#IndexCreation.Col10 = #TempIndexCreation.Col10,
    #IndexCreation.Col11 = #TempIndexCreation.Col11,#IndexCreation.Col12 = #TempIndexCreation.Col12,#IndexCreation.Col13 = #TempIndexCreation.Col13,#IndexCreation.Col14 = #TempIndexCreation.Col14,#IndexCreation.Col15 = #TempIndexCreation.Col15,
  #IndexCreation.Col16 = #TempIndexCreation.Col16
FROM #IndexCreation, #TempIndexCreation
WHERE #IndexCreation.[Table] = #TempIndexCreation.[Table] AND #IndexCreation.Ix_Handle_Key = #TempIndexCreation.Ix_Handle_Key

SELECT 'Possible Redundant Indexes (no INCLUDEs)' AS Comments, I.DBName, I.[Table], I.[Ix_Handle_Key] AS IndexID, 'IX_' + CAST(I.[Ix_Handle_Key] AS NVARCHAR) AS IndexName, I.[Equality] AS AllColName
FROM #IndexCreation I JOIN #IndexCreation I2
ON I.[Table] = I2.[Table] AND I.[Ix_Handle_Key] <> I2.[Ix_Handle_Key] AND I.Col1 = I2.Col2 AND I.Col2 = I2.Col1
ORDER BY I.[Table],I.[Ix_Handle_Key]

DROP TABLE #IndexCreation
DROP TABLE #IndexCreationSec
DROP TABLE #TempIndexCreation
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO