Determine the last database backup in SQL Server with T-SQL

SELECT
   A.[Server], 
   A.database_name,
   A.last_db_backup_date,   
   B.backup_size/1024/1024 as backup_size, 
   B.physical_device_name ,
    'Yes' as 'file_exists'
into dbo.##BackupReport -- TEMPORARY TABLE
FROM
   (
   SELECT  
       CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server,
       msdb.dbo.backupset.database_name, 
       MAX(msdb.dbo.backupset.backup_finish_date) AS last_db_backup_date
   FROM    msdb.dbo.backupmediafamily 
       INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id
   WHERE   msdb..backupset.type = 'D' and msdb..backupmediafamily.device_type=2
   GROUP BY
       msdb.dbo.backupset.database_name 
   ) AS A   
   LEFT JOIN 
   (
   SELECT  
   CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server,
   msdb.dbo.backupset.database_name, 
   msdb.dbo.backupset.backup_start_date, 
   msdb.dbo.backupset.backup_finish_date, 
   msdb.dbo.backupset.backup_size,  
   msdb.dbo.backupmediafamily.physical_device_name,   
   msdb.dbo.backupset.description
FROM   msdb.dbo.backupmediafamily 
   INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id 
WHERE  msdb..backupset.type = 'D' and msdb..backupmediafamily.device_type=2
   ) AS B
   ON A.[server] = B.[server] AND A.[database_name] = B.[database_name] AND A.[last_db_backup_date] = B.[backup_finish_date]
ORDER BY 
   A.database_name
--DECLARATION
DECLARE @FileName varchar(1000)
DECLARE @File_Exists int
--DECLARE THE CURSOR
DECLARE db_cursor CURSOR FOR
SELECT 
   B.physical_device_name
FROM
   (
   SELECT  
       CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server,
       msdb.dbo.backupset.database_name, 
       MAX(msdb.dbo.backupset.backup_finish_date) AS last_db_backup_date
   FROM    msdb.dbo.backupmediafamily 
       INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id
   WHERE   msdb..backupset.type = 'D' and msdb..backupmediafamily.device_type=2
   GROUP BY
       msdb.dbo.backupset.database_name 
   ) AS A   
   LEFT JOIN 
   (
   SELECT  
   CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server,
   msdb.dbo.backupset.database_name, 
   msdb.dbo.backupset.backup_start_date, 
   msdb.dbo.backupset.backup_finish_date, 
   msdb.dbo.backupset.backup_size,  
   msdb.dbo.backupmediafamily.physical_device_name,   
   msdb.dbo.backupset.description
FROM   msdb.dbo.backupmediafamily 
   INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id 
WHERE  msdb..backupset.type = 'D' and msdb..backupmediafamily.device_type=2
   ) AS B
   ON A.[server] = B.[server] AND A.[database_name] = B.[database_name] AND A.[last_db_backup_date] = B.[backup_finish_date]
ORDER BY 
   A.database_name
      --OPEN CURSOR
OPEN db_cursor  
--ASSIGNING VALUE TO CURSOR
FETCH NEXT FROM db_cursor INTO @FileName
--LOOPING
WHILE @@FETCH_STATUS = 0   --CONDITION
BEGIN  
EXEC Master.dbo.xp_fileexist @FileName, @File_Exists OUT --CHECK IF FILE EXIST OR NOT
IF @File_Exists = 1
update ##BackupReport set file_exists = 'Yes' where physical_device_name like @FileName
ELSE
update ##BackupReport set file_exists = 'No' where physical_device_name like @FileName
FETCH NEXT FROM db_cursor INTO @FileName --EXIT LOOP CONDITION
END  
--CLOSE CURSOR
CLOSE db_cursor  
DEALLOCATE db_cursor

--BACKUP REPORT

select Server as ServerName, database_name as DatabaseName, cast(last_db_backup_date as varchar(50)) as LastBackupDate ,backup_size as BackupSize ,physical_device_name as BackupPath ,file_exists as FileExists from ##BackupReport


--DROP TEMPORARY TABLE
Drop table ##BackupReport