create table from result set

how can i put the results of the command 'RESTORE
FILELISTONLY' into a table?Hi
create database test
go
backup database test to disk='d:\test1.bak'
go
Create table #test
(
LogicalName varchar(100),
PhysicalName varchar(100),
Type char(1),
FileGroupName varchar(100),
[Size]varchar(100),
[MaxSize]varchar(100)
)
insert into #test exec('RESTORE FILELISTONLY FROM disk= ''d:\test1.bak''')
go
select * from #test
go
drop database test
<anonymous@.discussions.microsoft.com> wrote in message
news:af6501c4076e$ed70b4c0$a501280a@.phx.gbl...
> how can i put the results of the command 'RESTORE
> FILELISTONLY' into a table?
>|||You can use INSERT ... EXEC. For example:
--FILELISTONLY
IF OBJECT_ID(N'tempdb..#FileList') IS NOT NULL
DROP TABLE #FileList
CREATE TABLE #FileList
(
LogicalName nvarchar(128) NOT NULL,
PhysicalName nvarchar(260) NOT NULL,
Type char(1) NOT NULL,
FileGroupName nvarchar(120) NULL,
Size numeric(20, 0) NOT NULL,
MaxSize numeric(20, 0) NOT NULL
)
INSERT INTO #FileList
EXEC('RESTORE FILELISTONLY FROM DISK=''C:\Backups\MyDatabase.bak''')
--HEADERONLY
IF OBJECT_ID(N'tempdb..#BackupHeader') IS NOT NULL
DROP TABLE #BackupHeader
CREATE TABLE #BackupHeader
(
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 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)
)
INSERT INTO #BackupHeader
EXEC ('RESTORE HEADERONLY FROM DISK=''C:\Backups\MyDatabase.bak''')
SELECT * FROM #FileList
SELECT * FROM #BackupHeader
DROP TABLE #FileList
DROP TABLE #BackupHeader
Hope this helps.
Dan Guzman
SQL Server MVP
<anonymous@.discussions.microsoft.com> wrote in message
news:af6501c4076e$ed70b4c0$a501280a@.phx.gbl...
> how can i put the results of the command 'RESTORE
> FILELISTONLY' into a table?
>sql