Results 1 to 3 of 3
  1. #1
    Join Date
    Sep 2003
    Posts
    1

    Unanswered: Copying Table Structure

    Hi List
    I need to copy the table structure of the table without the rows

    i have tried doing it by picking up table info from 'all_tab_columns'

    But i could not do it the query is

    I want to copy the table structure of test to foo

    create foo as select COLUMN_NAME,DATE_TYPE,DATE_LENGTH from all_tab_columns where TABLE_NAME='TEST';

    can someone guide me on this

    Thanks in advance

    Himalay

  2. #2
    Join Date
    Aug 2003
    Location
    SW Ohio
    Posts
    198

    Re: Copying Table Structure

    Originally posted by gopu
    Hi List
    I need to copy the table structure of the table without the rows

    i have tried doing it by picking up table info from 'all_tab_columns'

    But i could not do it the query is

    I want to copy the table structure of test to foo

    create foo as select COLUMN_NAME,DATE_TYPE,DATE_LENGTH from all_tab_columns where TABLE_NAME='TEST';

    can someone guide me on this

    Thanks in advance

    Himalay
    Try this:
    create table foo as (
    select * from test
    where <field> > (select max( <field>) from test) );


    This copies the table structure but because there is never something greater than itself in the table you get now rows.

    Fast quick and dirty.
    Jim P.

    Supoorting Oracle, MSSQL7, Sybase 8, & Pervasive. Confusion Reigns

  3. #3
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1

    Re: Copying Table Structure

    Originally posted by gopu
    Hi List
    I need to copy the table structure of the table without the rows

    i have tried doing it by picking up table info from 'all_tab_columns'

    But i could not do it the query is

    I want to copy the table structure of test to foo

    create foo as select COLUMN_NAME,DATE_TYPE,DATE_LENGTH from all_tab_columns where TABLE_NAME='TEST';

    can someone guide me on this

    Thanks in advance

    Himalay
    CREATE TABLE NEW_TABLE AS SELECT * FROM OLD_TABLE
    WHERE 1 = 2;

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •