looking for something like SHOW CREATE TABLE

After I do a
SELECT * INTO #temp_table FROM x WHERE y
I would like to see a CREATE TABLE statement for #temp_table somehow.Hi
"metaperl" wrote:
> After I do a
> SELECT * INTO #temp_table FROM x WHERE y
> I would like to see a CREATE TABLE statement for #temp_table somehow.
>
You can view the table in tempdb, but it will be called something like
#temp_table_____________________________12345678. The object browser (F8)in
Query Analyser should show it but the scripting options will not work!
The table structure would be the same as table x, so you can script that and
alter the script to remove contraints and indexes.
John|||On Jul 6, 7:48 am, John Bell <jbellnewspo...@.hotmail.com> wrote:
> The table structure would be the same as table x, so you can script that and
> alter the script to remove contraints and indexes.
table x was hypothetical. in fact, this final temp table is the result
of many joins of many temp tables... so it's much easier to see the
schema of this final output temp table than to trace back through all
the joins and try to figure out its schema that way
> John
Terrence!|||On Jul 6, 7:48 am, John Bell <jbellnewspo...@.hotmail.com> wrote:
> Hi
> "metaperl" wrote:
> > I would like to see a CREATE TABLE statement for #temp_table somehow.
> You can view the table in tempdb, but it will be called something like
> #temp_table_____________________________12345678. The object browser (F8)in
> Query Analyser should show it but the scripting options will not work!
What is tempdb? I am on MS-SQL 2000 still. I did not see such a table
in the Catalog (I use Synametrics WinSQL, we dont have anything else
installed for analyzing tables).|||Hi
"metaperl" wrote:
> On Jul 6, 7:48 am, John Bell <jbellnewspo...@.hotmail.com> wrote:
> > Hi
> >
> > "metaperl" wrote:
> >
> > > I would like to see a CREATE TABLE statement for #temp_table somehow.
> >
> > You can view the table in tempdb, but it will be called something like
> > #temp_table_____________________________12345678. The object browser (F8)in
> > Query Analyser should show it but the scripting options will not work!
> What is tempdb? I am on MS-SQL 2000 still. I did not see such a table
> in the Catalog (I use Synametrics WinSQL, we dont have anything else
> installed for analyzing tables).
>
Tempdb is one of the system databases used for things such as temporary
tables and is sometimes used for other system actions such as rebuilding
indexes.
You should download books online and read the administration and
architecture sections. SQL 2005 books online can be downloaded from
http://go.microsoft.com/fwlink/?linkid=50478 SQL 2000 can be downloaded from
http://technet.microsoft.com/en-us/sqlserver/bb331733.aspx
You probably have MSDE or SQLExpress in which case download Microsoft SQL
Server Management Studio Express
http://www.microsoft.com/downloads/details.aspx?FamilyID=C243A5AE-4BD1-4E3D-94B8-5A0F62BF7796&displaylang=en if you are using SQL Express or
if you have SQL 2000 the SQL Server Web Data Administrato
http://www.microsoft.com/downloads/details.aspx?FamilyID=c039a798-c57a-419e-acbc-2a332cb7f959&DisplayLang=en
If you don't want to do this, then you could write some DMO/SMO code to get
the table definitions (see Books Online). If you changed the statement to not
use a temporary table then you could get the definition of the table that you
have created rather than using the source tables. The T-SQL command sp_help
may give you enough information to create your own CREATE TABLE statement use
EXEC sp_help MyTable
John