Given a temp table, how can one script its DDL?

[jeljeljel]

I have an interesting problem I would like to solve.

Take the following sql...

--*********************

create table MainTable (id int, name varchar(50))

select * into #temptable from MainTable

drop #temptable

--*********************

How would I generate the create statement for the newly created temp
table from that sql?

I tried...

--*********************

create table MainTable (id int, name varchar(50))

select * into #temptable from MainTable

exec sp_helptext #temptable

drop #temptable

--*********************

but the sp_helptext call cannot find the temp table.

I realize that is a very simple example, but for queries that return
several dozen columns, this
would be a very helpful thing for me right now.

Thanks,
John

[Aaron Bertrand [SQL Server MVP]]

sp_helptext is for procedures/functions/triggers. Maybe you meant:

EXEC tempdb..sp_help '#temptable'

A

"jeljeljel" <livermo...@gmail.com> wrote in message
news:1191513907.5...@57g2000hsv.googlegroups.com...

[Russell Fields]

John,

In the example below, sp_helptext does not return table definitions in any
database, although it will show a view definition. (A view definition is
just more code, but a table definition is an object.) You can, run:
sp_help '#temptablename"
It returns the table definition, but not in a scriptable form.

However, in the particular case that you are describing, you can create a
real table in your database one time, then script out that table and change
the table name. E.g.

select * into dbo.poundsign_temptable from MainTable where 0 = 1

Generate the scripts for the poundsign_ tables. Then do a global change of
"poundsign_" to ""#" and you are in business. Drop all the poundsign_
tables afterward.

Now you have scripts for your #temp tables.

Vyas Kondreddi at http://vyaskn.tripod.com/code.htm has a COM object to
create tables. (But, since a #temp table only exists to the connection that
made it, his code will not touch a #temp table.)

RLF

"jeljeljel" <livermo...@gmail.com> wrote in message
news:1191513907.5...@57g2000hsv.googlegroups.com...

[jeljeljel]

On Oct 4, 12:31 pm, "Russell Fields" <russellfie...@nomail.com> wrote:
> John,
>
> In the example below, sp_helptext does not return table definitions in any
> database, although it will show a view definition. (A view definition is
> just more code, but a table definition is an object.) You can, run:
> sp_help '#temptablename"
> It returns the table definition, but not in a scriptable form.
>
> However, in the particular case that you are describing, you can create a
> real table in your database one time, then script out that table and change
> the table name. E.g.
>
> select * into dbo.poundsign_temptable from MainTable where 0 = 1
>
> Generate the scripts for the poundsign_ tables. Then do a global change of
> "poundsign_" to ""#" and you are in business. Drop all the poundsign_
> tables afterward.
>
> Now you have scripts for your #temp tables.
>

> Vyas Kondreddi athttp://vyaskn.tripod.com/code.htmhas a COM object to

> create tables. (But, since a #temp table only exists to the connection that
> made it, his code will not touch a #temp table.)
>
> RLF
>

> "jeljeljel" <livermore.j...@gmail.com> wrote in message

>
> news:1191513907.5...@57g2000hsv.googlegroups.com...
>
>
>
> >I have an interesting problem I would like to solve.
>
> > Take the following sql...
>
> > --*********************
>
> > create table MainTable (id int, name varchar(50))
>
> > select * into #temptable from MainTable
>
> > drop #temptable
>
> > --*********************
>
> > How would I generate the create statement for the newly created temp
> > table from that sql?
>
> > I tried...
>
> > --*********************
>
> > create table MainTable (id int, name varchar(50))
>
> > select * into #temptable from MainTable
>
> > exec sp_helptext #temptable
>
> > drop #temptable
>
> > --*********************
>
> > but the sp_helptext call cannot find the temp table.
>
> > I realize that is a very simple example, but for queries that return
> > several dozen columns, this
> > would be a very helpful thing for me right now.
>
> > Thanks,

> > John- Hide quoted text -
>
> - Show quoted text -

Thanks for the responses. They were helpful.