Finding the last date when Full / Diff / Transaction Log / File Level Backups Taken For All Databases

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,
(Select convert(varchar(25),Max(backup_finish_date), 100) +' File Location ' + bmf.physical_device_name
From msdb.dbo.backupset a,msdb.dbo.backupmediafamily bmf
Where a.database_name=e.database_name
and a.media_set_id=bmf.media_set_id
and bmf.media_set_id=e.media_set_id
and a.server_name = @@servername
and type='D'
Group by a.database_name,bmf.physical_device_name) as 'Full Backup Date & File Location',

(Select convert(varchar(25),Max(backup_finish_date) , 100) +' File Location '+ bmf.physical_device_name
From msdb.dbo.backupset b,msdb.dbo.backupmediafamily bmf
Where b.database_name=e.database_name
and b.media_set_id=bmf.media_set_id
and bmf.media_set_id=e.media_set_id
and b.server_name = @@servername
and type='L' Group by b.database_name,bmf.physical_device_name) 'Transaction Log Backup Date & File Location',

(Select convert(varchar(25),Max(backup_finish_date) , 100) +' File Location '+ bmf.physical_device_name
From msdb.dbo.backupset c,msdb.dbo.backupmediafamily bmf
Where c.database_name=e.database_name
and c.media_set_id=bmf.media_set_id
and bmf.media_set_id=e.media_set_id
and c.server_name = @@servername
and type='I' Group by c.database_name,bmf.physical_device_name) as 'Differential Backup Date & File Location',

(Select convert(varchar(25),Max(backup_finish_date) , 100) +' File Location '+ bmf.physical_device_name
From msdb.dbo.backupset d,msdb.dbo.backupmediafamily bmf
Where d.database_name=e.database_name
and d.media_set_id=bmf.media_set_id
and bmf.media_set_id=e.media_set_id
and d.server_name = @@servername
and type='F' Group by d.database_name,bmf.physical_device_name) as 'File Level Backup Date & File Location'
From msdb.dbo.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
from master..sysdatabases as record
where name not in (select distinct database_name from msdb.dbo.backupset)
and name not in ('tempdb','pubs','northwind','model')
order by 1,2