Here's a version that'll run on one database and produce results for all databases (assuming they're setup as linked servers on the main db).

Could be further improved by putting the type lookup into a permanent table and outputting results of dynamic sql to a temp table from where you could feed it into whatever additional queries / functionality you wanted.

declare @server_id int

declare @sql nvarchar(max)

set @server_id = -1

while exists (select top 1 1 from sys.servers where server_id > @server_id)

begin

select top 1 @server_id = server_id, @sql = QUOTENAME(name)

from sys.servers

where server_id > @server_id

order by server_id

set @sql = 'select *

from

(

select QUOTENAME(bs.server_name) + ''.'' + QUOTENAME(bs.database_name) DBName

, bs.type BackupTypeCode --included since it looks like this field could be null, so not restricted to the lookup values

, t.descrptn BackupType

, COALESCE(Convert(nvarchar(20), backup_finish_date, 113),''Backup Not Taken'') FinishDate

, COALESCE(Convert(nvarchar(20), backup_size, 101),''NA'') Size

, COALESCE(Convert(nvarchar(20), compressed_backup_size, 101),''NA'') CompressedSize

, COALESCE(Convert(nvarchar(20), backup_set_id, 101),''NA'') SetID

, name BackupName

, ROW_NUMBER() over (partition by bs.database_name, bs.type order by bs.backup_start_date desc) r

from ' + @sql + '.msdb.dbo.backupset bs

left outer join

(

select ''D'' id, ''Database'' descrptn

union select ''I'' ,''Differential database''

union select ''L'' ,''Log''

union select ''F'' ,''File or filegroup''

union select ''G'' ,''Differential file''

union select ''P'' ,''Partial''

union select ''Q'' ,''Differential partial''

) t on bs.type = t.id

) x where r = 1

order by DBName, BackupTypeCode

'

exec (@sql) --you could insert to a temp table / something like that to make use of the results later

end