Database last restore status report

use master
go
drop procedure sp_db_restore_status_report
go

create procedure sp_db_restore_status_report
as
begin

/*
select *
from master.sys.databases a  WITH (nolock)
where a.name like 'ces_%'
order by a.name

SELECT top 1 * FROM MSDB..RESTOREHISTORY WITH (nolock)
WHERE (DESTINATION_DATABASE_NAME = 'ces_aaanational')
ORDER BY RESTORE_DATE DESC
*/
if(OBJECT_ID('tempdb..#tmp')>1)
    drop table #tmp
   
Create table #tmp (db nvarchar(1000), restore_Date datetime)

insert into #tmp
select a.name ,
(
SELECT top 1 restore_date FROM MSDB..RESTOREHISTORY WITH (nolock)
WHERE (DESTINATION_DATABASE_NAME = a.name)
ORDER BY RESTORE_DATE DESC
) as last_restore_date
from master.sys.databases a  WITH (nolock)
where a.name like 'ces_%'

--select * From #tmp

if(OBJECT_ID('tempdb..#tmp_total')>1)
    drop table #tmp_total
   
select
(
select COUNT(*)
From #tmp
) as total_dbs,
(
select COUNT(*)
From #tmp
where isnull(restore_Date,'01/01/1900') > DATEADD(hh,-2,getdate())
) as restored_last_2_hours,
(
select COUNT(*)
From #tmp
where isnull(restore_Date,'01/01/1900') > DATEADD(hh,-8,getdate())
) as restored_last_8_hours,
(
select COUNT(*)
From #tmp
where isnull(restore_Date,'01/01/1900') > DATEADD(hh,-24,getdate())
) as restored_last_24_hours,
(
select COUNT(*)
From #tmp
where isnull(restore_Date,'01/01/1900') < DATEADD(hh,-24,getdate())
)  as restored_older_24_hours
into #tmp_total

--select * From #tmp_total

DECLARE @tableHTML  NVARCHAR(MAX) , @subjectstring nvarchar(200);

SET @tableHTML =
    N'

Database Restore Report from '+@@SERVERNAME+'

' +
    N'' +
    N' ' +
    N'' +
    N' ' +
    CAST ( ( SELECT td = total_dbs,       '',
                    td = restored_last_2_hours,       '',
                    td = restored_last_8_hours, '',
                    td = restored_last_24_hours, '',
                    td = restored_older_24_hours
              FROM #tmp_total
              FOR XML PATH('tr'), TYPE
    ) AS NVARCHAR(MAX) ) +
    N'

Total Databases	Restored in last 2 hours	Restored in last 8 hours	Restored in last 24 hours	Restored over 24 hours

' ;
   
print @tableHTML
   
set @subjectstring = 'Database Restore Report from '+@@SERVERNAME
EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'default',
    @recipients ='palexander@csod.com;ayegudkin@csod.com;jferris@csod.com',
    @body = @tableHTML,
    @body_format = 'HTML' ,
    @subject = @subjectstring;
   
end