View backup file details with TSQL

In order to automate testing of backups it’s useful to be able to query backup files to access various bits of meta-data. We can do this with the RESTORE FILELISTONLY TSQL command. In the simplest format the command is as follows;

This doesn’t help us much with automation. We need to get this resultset into a table so we can make use of the information it provides in our stored procedures. We can do this with a bit of trickery with the EXECUTE command.