SQL Server / Missing Indexes Query / DMV / Email Alert

EXECUTE msdb.dbo.sysmail_help_profileaccount_sp;

DECLARE
@EmailSubject varchar(100),
@TextTitle varchar(100),
@TableHTML nvarchar(max),
@Body nvarchar(max),
@DBName varchar(50)
SET @DBName = 'Database Name'
SET @EmailSubject = 'Missing Index Suggestion For ' + @DBName + ' Database'
SET @TextTitle = 'Missing Index Suggestion For ' + @DBName + ' Database'
SET @TableHTML =
''+'

<style>'+<br />-- Data cells styles / font size etc<br />'td {border:1px solid #ddd;padding-left:5px;padding-right:5px;padding-top:1px;padding-bottom:1px;font-size:10pt}'+<br />'</style>

'+
''+
-- TextTitle style
'
<div style="margin-top: 15px; margin-left: 15px; margin-bottom: 15px; font-weight: bold; font-size: 13pt; font-family: calibri;">' + @TextTitle +'</div>
' +
-- Color and columns names
'
<div style="font-family: Calibri;">'+''+''+ -- Database Name
''+ -- Table Name
''+ -- Equality Columns
''+ -- Inequality Columns
''+  -- Included Columns
''+ -- Overal Impact Value
''+ -- Create Index Statement
'
<table>
<tbody>
<tr bgcolor="#00881d">
<td align="left"><span style="color: white; font-family: calibri;"><b>Database Name</b></span></td>
<td align="left"><span style="color: white; font-family: calibri;"><b>Table Name</b></span></td>
<td align="left"><span style="color: white; font-family: calibri;"><b>Equality Columns</b></span></td>
<td align="left"><span style="color: white; font-family: calibri;"><b>Inequality Columns</b></span></td>
<td align="left"><span style="color: white; font-family: calibri;"><b>Included Columns</b></span></td>
<td align="left"><span style="color: white; font-family: calibri;"><b>Overal Impact Value</b></span></td>
<td align="left"><span style="color: white; font-family: calibri;"><b>Create Index Statement</b></span></td>
</tr>
</tbody>
</table>
</div>
'
----------------------------------------------------------
----- Querying the plan cache for missing indexes --------
----------------------------------------------------------
IF OBJECT_ID('tempdb..#MissingIndexTable') IS NOT NULL DROP Table #MissingIndexTable
SELECT
@DBName as db_name,
mid.statement as table_name,
IsNull(mid.equality_columns, '') as equality_columns,
IsNull(mid.inequality_columns, '') as inequality_columns,
IsNull(mid.included_columns, '') as included_columns,
migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans) AS overal_impact_value,
'CREATE INDEX [NCIX_' + REPLACE(REPLACE(REPLACE(mid.equality_columns, '[', ''), ']',''),',','_')+ ']'
+ ' ON ' + mid.statement
+ ' (' + ISNULL (mid.equality_columns,'')
+ CASE WHEN mid.equality_columns IS NOT NULL AND mid.inequality_columns IS NOT NULL THEN ',' ELSE '' END
+ ISNULL (mid.inequality_columns, '')
+ ')'
+ ISNULL (' INCLUDE (' + mid.included_columns + ')', '') AS create_index_statement
into #MissingIndexTable
FROM sys.dm_db_missing_index_groups mig
INNER JOIN sys.dm_db_missing_index_group_stats migs ON migs.group_handle = mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details mid ON mig.index_handle = mid.index_handle
LEFT OUTER JOIN sys.databases dbs ON mid.database_id=dbs.database_id
WHERE migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans) > 1000 and dbs.database_id > 4
----------------------------------------------------------
----------------------------------------------------------
SELECT @Body =(
SELECT
td = @DBName,
td = table_name,
td = equality_columns,
td = inequality_columns,
td = included_columns,
td = CONVERT(DECIMAL(16,2),overal_impact_value),
td = create_index_statement
FROM
#MissingIndexTable
ORDER BY CONVERT(DECIMAL(16,2), overal_impact_value) desc
for XML raw('tr'), elements)
SET @body = REPLACE(@body, '

', '

<span style="font-family: calibri;"><span style="font-family: calibri;">')
SET @tableHTML = @tableHTML + @body + '</span></span>

'
SET @tableHTML = '
<div style="color: black; font-size: 8pt; font-family: Calibri; width: auto;">' + @tableHTML + '</div>
'
exec msdb.dbo.sp_send_dbmail
@profile_name = 'Your Database Mail Profile',
@recipients = 'recipient@email.com',
@body = @tableHTML,
@subject = @emailSubject,
@body_format = 'HTML'