SQL _as_SQL_SendMissingIndexReport

_as_SQL_SendMissingIndexReport
CREATE PROCEDURE _as_SQL_SendMissingIndexReport
--drop procedure SendMissingIndexReport
with encryption
AS
BEGIN
    DECLARE @tableHTML NVARCHAR(MAX);

    SET @tableHTML =
    N'
    <html>
        <head>
            <style>
                table {
                    width: 100%;
                    border-collapse: collapse;
                }
                th {
                    background-color: #003366;
                    color: #d3d3d3;
                    padding: 8px;
                    text-align: left;
                }
                td {
                    padding: 8px;
                    text-align: left;
                    border-bottom: 1px solid #ddd;
                }
                .impact {
                    text-align: right;
                }
                tr:nth-child(even) {
                    background-color: #f2f2f2;
                }
            </style>
        </head>
        <body>
            <table>
                <thead>
                    <tr>
                        <th>Impact</th>
                        <th>Table</th>
                        <th>CreateIndexStatement</th>
                        <th>Equality Columns</th>
                        <th>Inequality Columns</th>
                        <th>Included Columns</th>
                    </tr>
                </thead>
                <tbody>';

    ;WITH ImpactCTE AS (
        SELECT
            [Impact] = (avg_total_user_cost * avg_user_impact) * (user_seeks + user_scans),
            [Table] = [statement],
            [CreateIndexStatement] = 'CREATE NONCLUSTERED INDEX ix_' + sys.objects.name COLLATE DATABASE_DEFAULT + '_' +
                                     REPLACE(REPLACE(REPLACE(ISNULL(mid.equality_columns, '') + ISNULL(mid.inequality_columns, ''), '[', ''), ']', ''), ', ', '_') +
                                     ' ON ' + [statement] +
                                     ' ( ' + ISNULL(mid.equality_columns, '') +
                                     CASE WHEN mid.inequality_columns IS NULL THEN '' ELSE
                                         CASE WHEN mid.equality_columns IS NULL THEN '' ELSE ',' END + mid.inequality_columns END + ' ) ' +
                                     CASE WHEN mid.included_columns IS NULL THEN '' ELSE 'INCLUDE (' + mid.included_columns + ')' END + ';',
            mid.equality_columns,
            mid.inequality_columns,
            mid.included_columns
        FROM sys.dm_db_missing_index_group_stats AS migs
            INNER JOIN sys.dm_db_missing_index_groups AS mig ON migs.group_handle = mig.index_group_handle
            INNER JOIN sys.dm_db_missing_index_details AS mid ON mig.index_handle = mid.index_handle
            INNER JOIN sys.objects WITH (nolock) ON mid.OBJECT_ID = sys.objects.OBJECT_ID
        WHERE (migs.group_handle IN (
                    SELECT TOP (500) group_handle
                    FROM sys.dm_db_missing_index_group_stats WITH (nolock)
                    ORDER BY (avg_total_user_cost * avg_user_impact) * (user_seeks + user_scans) DESC
                ))
            AND OBJECTPROPERTY(sys.objects.OBJECT_ID, 'isusertable') = 1
    )
    SELECT *
    INTO #FilteredResults
    FROM ImpactCTE
    WHERE Impact > 50000
    ORDER BY [Impact] DESC, [CreateIndexStatement] DESC;

    DECLARE @Impact NVARCHAR(255), @Table NVARCHAR(MAX), @CreateIndexStatement NVARCHAR(MAX), @EqualityColumns NVARCHAR(MAX), @InequalityColumns NVARCHAR(MAX), @IncludedColumns NVARCHAR(MAX);

    DECLARE db_cursor CURSOR FOR
    SELECT Impact, [Table], [CreateIndexStatement], equality_columns, inequality_columns, included_columns
    FROM #FilteredResults
	ORDER BY Impact desc;

    OPEN db_cursor;
    FETCH NEXT FROM db_cursor INTO @Impact, @Table, @CreateIndexStatement, @EqualityColumns, @InequalityColumns, @IncludedColumns;

    WHILE @@FETCH_STATUS = 0
    BEGIN
        SET @tableHTML = @tableHTML +
        N'<tr>
            <td class="impact">' + CAST(@Impact AS NVARCHAR(MAX)) + N'</td>
            <td>' + @Table + N'</td>
            <td>' + @CreateIndexStatement + N'</td>
            <td>' + ISNULL(@EqualityColumns, '') + N'</td>
            <td>' + ISNULL(@InequalityColumns, '') + N'</td>
            <td>' + ISNULL(@IncludedColumns, '') + N'</td>
        </tr>';

        FETCH NEXT FROM db_cursor INTO @Impact, @Table, @CreateIndexStatement, @EqualityColumns, @InequalityColumns, @IncludedColumns;
    END;

    CLOSE db_cursor;
    DEALLOCATE db_cursor;

    SET @tableHTML = @tableHTML +
    N'        </tbody>
            </table>
        </body>
    </html>';

    EXEC msdb.dbo.sp_send_dbmail
        @profile_name = 'Ambro DB Mail',
        @recipients = 'alex+AmbroSQL@asamco.com',
        @subject = 'Missing Index Report - Ambro',
        @body = @tableHTML,
        @body_format = 'HTML';

    DROP TABLE #FilteredResults;
END;
GO
Space shortcuts

Page tree
