How do capture the result set from RESTORE FILELISTONLY FROM DISK... so I can query it?

Question

How do I capture the output from the following SQL statement so I can query the resultset?:

RESTORE FILELISTONLY FROM 
    DISK = N''D:\Restores\MyBackup.BAK'' WITH NOUNLOAD,  
    FILE = 1

Do I need to create a temporary table and then do something like?:

INSERT #tmp EXEC ('RESTORE FILELISTONLY FROM  
    DISK = N''D:\Restores\KevsProfilerTraces.BAK'' WITH  NOUNLOAD,  FILE = 1')

I basically want to query the LogicalName and PhysicalName columns for some management tasks.

Or is there an easier way?

Answer 1

If your logic is in T-SQL then the only way is using INSERT ... EXEC ... as you already have in your post. It can be a #temp table or it can be a table @variable.

Other alternatives are to move the logic out of T-SQL into CLR procedures or into SSIS workflows.