Backup Verification Script - Backup when and what type

Select Distinct convert(varchar(25),@@Servername) as Servername,
            convert(varchar(30),e.database_name) as DBname,
            convert(varchar(20),convert(sysname,DatabasePropertyEx(e.database_name,'Recovery') )) as Recovery_model,
database_creation_date,backup_start_date,backup_finish_date,
           
(Select  convert(varchar(25),Max(backup_finish_date) , 100)
              From msdb..backupset a
              Where a.database_name=e.database_name
                and a.server_name  = @@servername
                and type='D'
              Group by a.database_name) Database_Bk,

             (Select convert(varchar(25),Max(backup_finish_date) , 100)
                From msdb..backupset b
               Where b.database_name=e.database_name
                and b.server_name  = @@servername
                 and type='L' Group by b.database_name) Log_Bk,

             (Select convert(varchar(25),Max(backup_finish_date) , 100)
                From msdb..backupset c
               Where c.database_name=e.database_name
                and c.server_name  = @@servername
                 and type='I' Group by c.database_name) Diff_Bk,

             (Select convert(varchar(25),Max(backup_finish_date) , 100)
                From msdb..backupset d
               Where d.database_name=e.database_name
                and  d.server_name  = @@servername
                 and type='F' Group by d.database_name) File_Bk

 From msdb..backupset e
Where e.database_name Not in ('tempdb','pubs','northwind','model')
  and e.server_name = @@Servername
  and e.database_name in (Select Distinct name from master..sysdatabases)

--never backed up
Union all
select Distinct convert(varchar(25),@@Servername) as Servername,
            convert(varchar(30),name) as DBname,
            convert(varchar(20),convert(sysname,DatabasePropertyEx(name,'Recovery'))),

NULL, NULL , NULL, NULL, NULL, NULL, NULL
from master..sysdatabases as record
where name not in (select distinct database_name from msdb..backupset)
and name not in ('tempdb','pubs','northwind','model')
order by 1,2