Query to find last differential backups of all databases - SQL Server

Query to find last differential backups of all databases - SQL Server

Run below query to find last differential backups of all databases in SQL Server.

SELECT  name ,
            recovery_model_desc ,
            state_desc ,
            i AS 'Last Differential Backup'
    FROM    ( SELECT    db.name ,
                        db.state_desc ,
                        db.recovery_model_desc ,
                        type ,
                        backup_finish_date
              FROM      master.sys.databases db
                        LEFT OUTER JOIN msdb.dbo.backupset a ON a.database_name = db.name
            ) AS Sourcetable
        PIVOT
            ( MAX(backup_finish_date) FOR type IN ( i) ) AS MostRecentBackup