Send report to email (HTML) I have a procedure with two temporary tables

CREATE TABLE #temp       
  ( databasename VARCHAR(100),  
    objectname VARCHAR(100), 
    index_id INT,
    indexname  VARCHAR(100), 
    avg_fragmentation_percent FLOAT,
    indextype VARCHAR(100) 
  )       


CREATE TABLE #temp2       
( 
    databasename VARCHAR(100), 
    objectname VARCHAR(100),
    index_id INT,
    indexname VARCHAR(100), 
    avg_fragmentation_percent FLOAT,
    indextype VARCHAR(100)   
)

INSERT INTO #temp (databasename, 
objectname,index_id, 
indexname,
avg_fragmentation_percent,
indextype)
EXEC master.sys.Sp_msforeachdb
' USE [?] SELECT db_name() as DatabaseName, OBJECT_NAME (a.object_id) as 
 ObjectName,
a.index_id, 
b.name as IndexName, 
avg_fragmentation_in_percent, 
index_type_desc
FROM sys.dm_db_index_physical_stats (db_id(), NULL, NULL, NULL, NULL) AS a 
JOIN sys.indexes AS b
ON a.object_id = b.object_id AND a.index_id = b.index_id 
WHERE b.index_id &lt;&gt; 0 and avg_fragmentation_in_percent &lt;&gt;0'

INSERT INTO #temp2 (databasename, objectname,index_id, 
indexname,avg_fragmentation_percent,indextype)
EXEC master.sys.Sp_msforeachdb
' USE [?] SELECT db_name() as DatabaseName, OBJECT_NAME (a.object_id) as 
 ObjectName,
a.index_id, 
b.name as IndexName, 
avg_fragmentation_in_percent, 
index_type_desc
FROM sys.dm_db_index_physical_stats (db_id(), NULL, NULL, NULL, NULL) AS a 
JOIN sys.indexes AS b
ON a.object_id = b.object_id AND a.index_id = b.index_id 
WHERE b.index_id &lt;&gt; 0 and avg_fragmentation_in_percent &lt;&gt;0'


and want to send the result from the procedure to email. The problem is that this query works fine but when i execute this its not finding the temporary tables.

DECLARE @tableHTML  NVARCHAR(MAX) ;

SET @tableHTML =
    N'&lt;H1&gt;Report&lt;/H1&gt;' +
    N'&lt;table border="1"&gt;' +
    N'&lt;tr&gt;&lt;th&gt;ID&lt;/th&gt;&lt;th&gt;Table&lt;/th&gt;' +
    N'&lt;th&gt;Index&lt;/th&gt;&lt;th&gt;Before fragmentation&lt;/th&gt;&lt;th&gt;After fragmentation&lt;/th&gt;' +
    --N'&lt;th&gt;Expected Revenue&lt;/th&gt;&lt;/tr&gt;' +
    CAST ( ( SELECT td = T1.databasename,'',
                    td = T1.objectname, '',
                    td = T1.index_id, '',
                    td = T1.indexname, '',
                    td = T1.indextype,'',
                    td = T1.avg_fragmentation_percent AS 
                         avg_fragmentation_percent_pre_change, '',
                    td = T2.avg_fragmentation_percent AS 
                         avg_fragmentation_percent_post_change
              FROM #temp as T1
              INNER JOIN #temp2 AS T2
              ON T1.databasename = T2.databasename
              AND T1.objectname = T2.objectname
              AND T1.index_id = T2.index_id
              AND T1.indexname = T2.indexname
              AND T1.indextype = T2.indextype
              WHERE T1.databasename not in  ('master',
                                             'model',
                                             'msdb',
                                             'tempdb',
                                             'reportServer',
                                             'ReportServerTempDB')             
              FOR XML PATH('tr'), TYPE 
    ) AS NVARCHAR(MAX) ) +
    N'&lt;/table&gt;' ;


EXEC msdb.dbo.sp_send_dbmail @recipients='example@mail.com',
@profile_name='example@mail.com',
    @subject = 'Report',
    @body = @tableHTML,
    @body_format = 'HTML';
Your insert to #temp and #temp2 works perfectly fine.

I believe you are getting the below error.


  Msg 156, Level 15, State 1, Line 70 Incorrect syntax near the keyword
  'AS'.


This error is due to the explicit aliasing on the below part of the code.

td = T1.avg_fragmentation_percent AS avg_fragmentation_percent_pre_change, '',
td = T2.avg_fragmentation_percent AS avg_fragmentation_percent_post_change


since you have already aliased it as 'td'. it will become like this

td = T1.avg_fragmentation_percent , '',
td = T2.avg_fragmentation_percent 


Remove the explicit aliasing using AS and run the code and see it works. I did test run 
and it worked.