DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: Create table statements from existing db

  1. #1
    Join Date
    May 2002
    Posts
    3
    I searched the forums to no avail.

    I need to generate the create scripts for an existing users tables.

    There are 150 or so tables and I dont want to create them myself by doing a describe table xxx and then pasting it into a create table script.


  2. #2
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    set pages 0
    set linesize 2222
    spool doit.sql
    select 'CREATE TABLE NEW_' || table_name || ' AS SELECT * FROM ' || table_name || ';'
    from user_tables
    /
    spool off
    @doit.sql
    Jeff Hunter

  3. #3
    Join Date
    May 2002
    Posts
    2,645
    What about constraints? You won't get those by doing create table ... as select * from ....

    You can do an export with show=y or indexfile=filename to get the DDL. Show=y is hard to read - lines will be wrapped; indexfile is easier to read, but you have to do some minor editing on the output file.

  4. #4
    Join Date
    May 2002
    Posts
    3

    The script above does give me close to what I needed.

    I just downloaded and installed the freeware version of TOAD (tool for oracle application developers) and it seems to give me what I want.

    Toad gives me pk, constraints, triggers even table data if i wanted.

  5. #5
    Join Date
    May 2002
    Posts
    193
    if you want to try out creating the tables and the related indexes and constraints, Probably try this method..

    from the full database export you can generate a script file by this command..

    imp username/password file=path of the export file full=y indexfile=filename

    this contains the create information and you could probably edit it before using it...


    check for the syntax and try this out on the test machine...


    Do let me know if this was usefull

    K.Diwakar(diwakar_2000@lycos.com)

  6. #6
    Join Date
    Jun 2002
    Posts
    64
    Hi,

    I believe What Divakar said is the best way.


    Cheers

  7. #7
    Join Date
    May 2002
    Location
    California, USA
    Posts
    175

    Cool

    If you need a tool - that would be DBArtisan (15 days evaluation). Can extract any DB object DDL. It is on: http://www.embarcadero.com

    Other ways of getting this done are described Here

    Hope that helps,

    clio_usa
    OCP DBA 8/8i/9i
    visit our web site: Here


Posting Permissions

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


Click Here to Expand Forum to Full Width