SQL SERVER: Uses RESTORE HEADERONLY to generate a temp table. The output columns are adjusted for the sql server version queried.

restore_headeronly_into_temp_table.sql

/*
	Original script from here: https://karaszi.com/restore-all-databases-from-a-number-of-backup-files
	Tim C: made a modification so the script adjusts the columns per the SQL Server version
*/
DECLARE @backup_file_path VARCHAR(500) = 'path to backup file.bak'

IF OBJECT_ID('tempdb..#restore_headeronly') IS NOT NULL BEGIN 
	DROP TABLE #restore_headeronly 
END

CREATE TABLE #restore_headeronly (
    BackupName NVARCHAR(128),
    BackupDescription NVARCHAR(255),
    BackupType SMALLINT,
    ExpirationDate DATETIME,
    Compressed TINYINT,
    Position SMALLINT,
    DeviceType TINYINT,
    UserName NVARCHAR(128),
    ServerName NVARCHAR(128),
    DatabaseName NVARCHAR(128),
    DatabaseVersion BIGINT,
    DatabaseCreationDate DATETIME,
    BackupSize NUMERIC(20, 0),
    FirstLSN NUMERIC(25, 0),
    LastLSN NUMERIC(25, 0),
    CheckpointLSN NUMERIC(25, 0),
    DatabaseBackupLSN NUMERIC(25, 0),
    BackupStartDate DATETIME,
    BackupFinishDate DATETIME,
    SortOrder SMALLINT,
    [CodePage] SMALLINT,
    UnicodeLocaleId BIGINT,
    UnicodeComparisonStyle BIGINT,
    CompatibilityLevel TINYINT,
    SoftwareVendorId BIGINT,
    SoftwareVersionMajor BIGINT,
    SoftwareVersionMinor BIGINT,
    SoftwareVersionBuild BIGINT,
    MachineName NVARCHAR(128),
    Flags BIGINT,
    BindingID UNIQUEIDENTIFIER,
    RecoveryForkID UNIQUEIDENTIFIER,
    Collation NVARCHAR(128),
    FamilyGUID UNIQUEIDENTIFIER,
    HasBulkLoggedData BIGINT,
    IsSnapshot BIGINT,
    IsReadOnly BIGINT,
    IsSingleUser BIGINT,
    HasBackupChecksums BIGINT,
    IsDamaged BIGINT,
    BeginsLogChain BIGINT,
    HasIncompleteMetaData BIGINT,
    IsForceOffline BIGINT,
    IsCopyOnly BIGINT,
    FirstRecoveryForkID UNIQUEIDENTIFIER,
    ForkPointLSN NUMERIC(25, 0),
    RecoveryModel NVARCHAR(128),
    DifferentialBaseLSN NUMERIC(25, 0),
    DifferentialBaseGUID UNIQUEIDENTIFIER,
    BackupTypeDescription NVARCHAR(128),
    BackupSetGUID UNIQUEIDENTIFIER,
    CompressedBackupSize BIGINT
);

DECLARE @major_version INT = CAST(SERVERPROPERTY('ProductMajorVersion') AS INT)

-- this column was added in 2012
IF @major_version >= 11 BEGIN    
	ALTER TABLE #restore_headeronly	ADD 
		Containment BIGINT
END

-- these columns were added in 2014
IF @major_version >= 12 BEGIN   
	ALTER TABLE #restore_headeronly	ADD 
		KeyAlgorithm NVARCHAR(32),
		EncryptorThumbprint VARBINARY(20),
		EncryptorType NVARCHAR(23)
END

-- cant use explicit columns here because of the version differences
INSERT INTO #restore_headeronly 
EXEC('RESTORE HEADERONLY FROM DISK = ''' + @backup_file_path + '''') 

-- get the max file number in case they are appending to the backups
DECLARE @file_number INT = (SELECT MAX(Position) FROM #restore_headeronly)

SELECT @file_number AS [latest_file_number]
SELECT * FROM #restore_headeronly