UnPivot The Output Of RESTORE HEADERONLY

In short, I wanted to take this...

...and "flip it" to this:

My first thought was to try forcing the RESTORE HEADERONLY output into a temp table. I felt confident this would work:

SELECT * INTO #TempTable FROM OPENROWSET('SQLNCLI', 'Server=.\SQL2014;Trusted_Connection=yes;',
     'EXEC (''RESTORE HEADERONLY FROM DISK = N''''D:\Backup\SomeApp9_Template.bak'''''')');

But I was wrong, as this error message clearly shows:

Msg 11527, Level 16, State 1, Procedure sp_describe_first_result_set, Line 9
The metadata could not be determined because statement 'RESTORE HEADERONLY FROM DISK = N'D:\Backup\SomeApp9_Template.bak'' does not support metadata discovery.

Surely something like this will work:

INSERT INTO #TempTable 
EXEC ('RESTORE HEADERONLY FROM DISK = N''D:\Backup\SomeApp9_Template.bak'' ')

But that means I have to define a temp table that matches the output of RESTORE HEADERONLY. Damn. That's a wide table I really didn't want to have to create. But it looks like I have to. <Grumble/> You know, I may want to do this again someday for another backup. So instead of defining a temp table, I'll create a reusable TABLE TYPE.

CREATE TYPE dbo.Header_Only AS TABLE (
    BackupName nvarchar(128),
    BackupDescription nvarchar(255),
    BackupType smallint,
    ExpirationDate datetime,
    Compressed BIT,
    Position smallint,
    DeviceType tinyint,
    UserName nvarchar(128),
    ServerName nvarchar(128),
    DatabaseName nvarchar(128),
    DatabaseVersion int,
    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 int,
    UnicodeComparisonStyle int,
    CompatibilityLevel tinyint,
    SoftwareVendorId int,
    SoftwareVersionMajor int,
    SoftwareVersionMinor int,
    SoftwareVersionBuild int,
    MachineName nvarchar(128),
    Flags int,
    BindingID uniqueidentifier,
    RecoveryForkID uniqueidentifier,
    Collation nvarchar(128),
    FamilyGUID uniqueidentifier,
    HasBulkLoggedData bit,
    IsSnapshot bit,
    IsReadOnly bit,
    IsSingleUser bit,
    HasBackupChecksums bit,
    IsDamaged bit,
    BeginsLogChain bit,
    HasIncompleteMetaData bit,
    IsForceOffline bit,
    IsCopyOnly bit,
    FirstRecoveryForkID uniqueidentifier,
    ForkPointLSN numeric(25,0) NULL,
    RecoveryModel nvarchar(60),
    DifferentialBaseLSN numeric(25,0) NULL,
    DifferentialBaseGUID uniqueidentifier,
    BackupTypeDescription nvarchar(60),
    BackupSetGUID uniqueidentifier NULL,
    CompressedBackupSize bigint,
    containment tinyint not NULL
)
GO

Now that I've got the TABLE TYPE, I'll use it to create a temp table, and insert the output of the RESTORE HEADERONLY statement into it via EXEC and a "RESTORE" string. So far, so good.

DECLARE @HO AS dbo.Header_Only;

SELECT *
INTO #HO
FROM @HO
WHERE 1 = 2

INSERT INTO #HO
EXEC ('RESTORE HEADERONLY FROM DISK = N''D:\Backup\SomeApp9_Template.bak'' ')

Next, I'll build a dynamic T-SQL query by iterating through the column names of the temp table (well, actually of the table type). The end result is a bunch of queries that select a static string (field name) and a single column (field value) from the temp table, UNION'ed together. The last "UNION ALL" is trimmed from the string. And the string is executed.

DECLARE @TSql VARCHAR(MAX) = '';
SELECT 
 @TSql = @TSql + 'SELECT ''' + c.name + ''' AS [Field], CAST([' + c.name + '] AS VARCHAR(MAX)) AS [Value] FROM #HO UNION ALL' + CHAR(13) + CHAR(10)
FROM sys.table_types t
JOIN sys.columns c 
 on c.object_id = t.type_table_object_id
WHERE t.name = 'Header_Only'
ORDER BY c.column_id

SET @TSql = LEFT(@TSql, LEN(@TSql) - 11) + CHAR(13) + CHAR(10)

EXEC (@TSql)

DROP TABLE #HO

You may be laughing and thinking "Gee, that's a lot of work." And you'd be right. But five years from now when I need to do this again, I'll have the last laugh. Bwah ha ha ha!