Find last Full/Diff/T-log backup date of all databases

--Find last Full/Diff/T-log backup date of all databases.

set nocount on
go
set quoted_identifier off

declare @dbname as varchar(80)
declare @msgdb as varchar(100)
declare @dbbkpname as varchar(80)
declare @dypart1 as varchar(2)
declare @dypart2 as varchar(3)
declare @dypart3 as varchar(4)
declare @currentdate as varchar(10)
declare @server_name as varchar(30)

select @server_name = @@servername
select @dypart1 = datepart(dd,getdate())
select @dypart2 = datename(mm,getdate())
select @dypart3 = datepart(yy,getdate())
select @currentdate= @dypart1 + @dypart2 + @dypart3

print '#####################################################################'
print '# SERVERNAME : ' + @server_name + ' DATE : ' + @currentdate +'#'
print '#####################################################################'

Print 'DatabaseName Full Diff TranLog'
Print '##########################################################################################################################################'

SELECT @@Servername [Server Name],SUBSTRING(s.name,1,50) AS 'DATABASE Name',
b.backup_start_date AS 'Full DB Backup Status',
c.backup_start_date AS 'Differential DB Backup Status',
d.backup_start_date AS 'Transaction Log Backup Status'
FROM master..sysdatabases s
LEFT OUTER JOIN msdb..backupset b
ON s.name = b.database_name
AND b.backup_start_date =
(SELECT MAX(backup_start_date)as 'Full DB Backup Status'
FROM msdb..backupset
WHERE database_name = b.database_name
AND type = 'D')
-- full database backups only, not log backups
LEFT OUTER JOIN msdb..backupset c
ON s.name = c.database_name
AND c.backup_start_date =
(SELECT MAX(backup_start_date)'Differential DB Backup Status'
FROM msdb..backupset
WHERE database_name = c.database_name
AND type = 'I')
LEFT OUTER JOIN msdb..backupset d
ON s.name = d.database_name
AND d.backup_start_date =
(SELECT MAX(backup_start_date)'Transaction Log Backup Status'
FROM msdb..backupset
WHERE database_name = d.database_name
AND type = 'L')
WHERE s.name in (select name from sys.databases)
ORDER BY s.name