Prev: How to encrypt/decrypt a table column?
Next: CTE on multiple tables?
From: Franz Mueller on 9 Jul 2008 15:47
Hi,

I want to store the results of restore filelistonly in a temp table
by using the following command.

INSERT INTO #RestoreFileListOnly
exec( 'restore filelistonly from disk = ''C:\Backups
\testdatabase.bak''')

I found this table definition on the internet:

CREATE TABLE #RestoreFileListOnly ( LogicalName nvarchar(128),
PhysicalName nvarchar(260),
Type char(1),
FileGroupName nvarchar(128),
Size numeric(20,0),
MaxSize numeric(20,0),
FileID bigint,
CreateLSN numeric(25,0),
DropLSN numeric(25,0) NULL,
UniqueID uniqueidentifier,
ReadOnlyLSN numeric(25,0) NULL,
ReadWriteLSN numeric(25,0) NULL,
BackupSizeInBytes bigint,
SourceBlockSize int,
FileGroupID int,
LogGroupGUID uniqueidentifier NULL,
DifferentialBaseLSN numeric(25,0) NULL,
DifferentialBaseGUID uniqueidentifier,
IsReadOnly bit,
IsPresent bit )

Everything works fine. However, I would like to know how I can come up
with all the column types and lengths? Can I get the information by
using
SQL statments or do I have to create a program to get this information?

Any help would be greatly appreciated,
Franz

From: SQL Menace on 9 Jul 2008 16:14
Franz, you can find that in Books On Line

Here is the link http://technet.microsoft.com/en-us/library/ms173778.aspx


Denis The SQL Menace
http://www.lessthandot.com/
http://sqlservercode.blogspot.com
http://sqlblog.com/blogs/denis_gobo/default.aspx



On Jul 9, 3:47 pm, Franz Mueller <fmuel...(a)planetd.de> wrote:
> Hi,
>
> I want to store the results of restore filelistonly in a temp table
> by using the following command.
>
> INSERT INTO #RestoreFileListOnly
> exec( 'restore filelistonly from disk = ''C:\Backups
> \testdatabase.bak''')
>
> I found this table definition on the internet:
>
> CREATE TABLE #RestoreFileListOnly ( LogicalName nvarchar(128),
> PhysicalName nvarchar(260),
> Type char(1),
> FileGroupName nvarchar(128),
> Size numeric(20,0),
> MaxSize numeric(20,0),
> FileID bigint,
> CreateLSN numeric(25,0),
> DropLSN numeric(25,0) NULL,
> UniqueID uniqueidentifier,
> ReadOnlyLSN numeric(25,0) NULL,
> ReadWriteLSN numeric(25,0) NULL,
> BackupSizeInBytes bigint,
> SourceBlockSize int,
> FileGroupID int,
> LogGroupGUID uniqueidentifier NULL,
> DifferentialBaseLSN numeric(25,0) NULL,
> DifferentialBaseGUID uniqueidentifier,
> IsReadOnly bit,
> IsPresent bit )
>
> Everything works fine. However, I would like to know how I can come up
> with all the column types and lengths? Can I get the information by
> using
> SQL statments or do I have to create a program to get this information?
>
> Any help would be greatly appreciated,
> Franz

From: Franz Mueller on 10 Jul 2008 01:35
Denis,

thank you very much. Is there a way to come up with this information
dynamically the same way that sp_help does for tables?

What I'm trying to do is creating a stored proc that takes
a command or sp_ call, and returns the CREATE TABLE statement
for the temp table so I don't have to do this work manually.

Franz

In article <44b77a4c-fc9c-486b-9285-af9e9e2c34a5
@s50g2000hsb.googlegroups.com>, denis.gobo(a)gmail.com says...
> Franz, you can find that in Books On Line
>
> Here is the link http://technet.microsoft.com/en-us/library/ms173778.aspx
>
>
> Denis The SQL Menace
> http://www.lessthandot.com/
> http://sqlservercode.blogspot.com
> http://sqlblog.com/blogs/denis_gobo/default.aspx
>
>
>
> On Jul 9, 3:47 pm, Franz Mueller <fmuel...(a)planetd.de> wrote:
> > Hi,
> >
> > I want to store the results of restore filelistonly in a temp table
> > by using the following command.
> >
> > INSERT INTO #RestoreFileListOnly
> > exec( 'restore filelistonly from disk = ''C:\Backups
> > \testdatabase.bak''')
> >
> > I found this table definition on the internet:
> >
> > CREATE TABLE #RestoreFileListOnly ( LogicalName nvarchar(128),
> > PhysicalName nvarchar(260),
> > Type char(1),
> > FileGroupName nvarchar(128),
> > Size numeric(20,0),
> > MaxSize numeric(20,0),
> > FileID bigint,
> > CreateLSN numeric(25,0),
> > DropLSN numeric(25,0) NULL,
> > UniqueID uniqueidentifier,
> > ReadOnlyLSN numeric(25,0) NULL,
> > ReadWriteLSN numeric(25,0) NULL,
> > BackupSizeInBytes bigint,
> > SourceBlockSize int,
> > FileGroupID int,
> > LogGroupGUID uniqueidentifier NULL,
> > DifferentialBaseLSN numeric(25,0) NULL,
> > DifferentialBaseGUID uniqueidentifier,
> > IsReadOnly bit,
> > IsPresent bit )
> >
> > Everything works fine. However, I would like to know how I can come up
> > with all the column types and lengths? Can I get the information by
> > using
> > SQL statments or do I have to create a program to get this information?
> >
From: SQL Menace on 10 Jul 2008 07:04
You could do it with a loopback query for a proc, take a look here
http://wiki.lessthandot.com/index.php/Store_The_Output_Of_A_Stored_Procedure_In_A_Table_Without_Creating_A_Table

However I did not test that with the restore command you are using,
but give it a shot


Denis The SQL Menace
http://www.lessthandot.com/
http://sqlservercode.blogspot.com
http://sqlblog.com/blogs/denis_gobo/default.aspx

On Jul 10, 1:35 am, Franz Mueller <fmuel...(a)planetd.de> wrote:
> Denis,
>
> thank you very much. Is there a way to come up with this information
> dynamically the same way that sp_help does for tables?
>
> What I'm trying to do is creating a stored proc that takes
> a command or sp_ call, and returns the CREATE TABLE statement
> for the temp table so I don't have to do this work manually.
>
> Franz
>
> In article <44b77a4c-fc9c-486b-9285-af9e9e2c34a5
> @s50g2000hsb.googlegroups.com>, denis.g...(a)gmail.com says...
>
>
>
> > Franz, you can find that in Books On Line
>
> > Here is the linkhttp://technet.microsoft.com/en-us/library/ms173778.aspx
>
> > Denis The SQL Menace
> >http://www.lessthandot.com/
> >http://sqlservercode.blogspot.com
> >http://sqlblog.com/blogs/denis_gobo/default.aspx
>
> > On Jul 9, 3:47 pm, Franz Mueller <fmuel...(a)planetd.de> wrote:
> > > Hi,
>
> > > I want to store the results of restore filelistonly in a temp table
> > > by using the following command.
>
> > > INSERT INTO #RestoreFileListOnly
> > > exec( 'restore filelistonly from disk = ''C:\Backups
> > > \testdatabase.bak''')
>
> > > I found this table definition on the internet:
>
> > > CREATE TABLE #RestoreFileListOnly ( LogicalName nvarchar(128),
> > > PhysicalName nvarchar(260),
> > > Type char(1),
> > > FileGroupName nvarchar(128),
> > > Size numeric(20,0),
> > > MaxSize numeric(20,0),
> > > FileID bigint,
> > > CreateLSN numeric(25,0),
> > > DropLSN numeric(25,0) NULL,
> > > UniqueID uniqueidentifier,
> > > ReadOnlyLSN numeric(25,0) NULL,
> > > ReadWriteLSN numeric(25,0) NULL,
> > > BackupSizeInBytes bigint,
> > > SourceBlockSize int,
> > > FileGroupID int,
> > > LogGroupGUID uniqueidentifier NULL,
> > > DifferentialBaseLSN numeric(25,0) NULL,
> > > DifferentialBaseGUID uniqueidentifier,
> > > IsReadOnly bit,
> > > IsPresent bit )
>
> > > Everything works fine. However, I would like to know how I can come up
> > > with all the column types and lengths? Can I get the information by
> > > using
> > > SQL statments or do I have to create a program to get this information?- Hide quoted text -
>
> - Show quoted text -

From: Franz Mueller on 10 Jul 2008 14:40
Hi Denis,

since I will be using this mainly for stored procedures this
is exactly what I need.

Thank you very much,
Franz

In article <d56ef902-08ca-49d3-b40a-8e93f4379219@
79g2000hsk.googlegroups.com>, denis.gobo(a)gmail.com says...
> You could do it with a loopback query for a proc, take a look here
> http://wiki.lessthandot.com/index.php/Store_The_Output_Of_A_Stored_Procedure_In_A_Table_Without_Creating_A_Table
>
> However I did not test that with the restore command you are using,
> but give it a shot
>
>
> Denis The SQL Menace
> http://www.lessthandot.com/
> http://sqlservercode.blogspot.com
> http://sqlblog.com/blogs/denis_gobo/default.aspx
>
> On Jul 10, 1:35 am, Franz Mueller <fmuel...(a)planetd.de> wrote:
> > Denis,
> >
> > thank you very much. Is there a way to come up with this information
> > dynamically the same way that sp_help does for tables?
> >
> > What I'm trying to do is creating a stored proc that takes
> > a command or sp_ call, and returns the CREATE TABLE statement
> > for the temp table so I don't have to do this work manually.
> >
> > Franz
> >
> > In article <44b77a4c-fc9c-486b-9285-af9e9e2c34a5
> > @s50g2000hsb.googlegroups.com>, denis.g...(a)gmail.com says...
> >
> >
> >
> > > Franz, you can find that in Books On Line
> >
> > > Here is the linkhttp://technet.microsoft.com/en-us/library/ms173778.aspx
> >
> > > Denis The SQL Menace
> > >http://www.lessthandot.com/
> > >http://sqlservercode.blogspot.com
> > >http://sqlblog.com/blogs/denis_gobo/default.aspx
> >
> > > On Jul 9, 3:47 pm, Franz Mueller <fmuel...(a)planetd.de> wrote:
> > > > Hi,
> >
> > > > I want to store the results of restore filelistonly in a temp table
> > > > by using the following command.
> >
> > > > INSERT INTO #RestoreFileListOnly
> > > > exec( 'restore filelistonly from disk = ''C:\Backups
> > > > \testdatabase.bak''')
> >
> > > > I found this table definition on the internet:
> >
 | 
Pages: 1
Prev: How to encrypt/decrypt a table column?
Next: CTE on multiple tables?