describe table to create table - cheeyoung/sqlplus-public GitHub Wiki

1. describe command and spool file (SQL*Plus)

SQL> spool desc01.out
SQL> desc (table name)
SQL> spool off
SQL> quit

For example,

SQL> spool desc01.out
SQL> desc PTLST_INFO
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 PROJ_NO                                   NOT NULL VARCHAR2(24)
...
 S_L_GB                                             VARCHAR2(1)

SQL> spool off

2. Save the column info.

For example,

$ vi desc01.out
     1  SQL> desc PTLST_INFO
     2   Name                                      Null?    Type
     3   ----------------------------------------- -------- ----------------------------
     4   PROJ_NO                                   NOT NULL VARCHAR2(24)
...
   451   S_L_GB                                             VARCHAR2(1)
   452
   453  SQL> spool off
:4,451w vi.out.1
:q
$ ls
desc01.out
vi.out.1

3. Run awk

awk -F' ' '{ if ($2 == "NOT" && $3 == "NULL") printf(",\t%s %s %s %s\n",$1,$4,$2,$3); else printf(",\t%s\t%s\n",$1,$2); }'

For example,

$ awk -F' ' '{ if ($2 == "NOT" && $3 == "NULL") printf(",\t%s %s %s %s\n",$1,$4,$2,$3); else printf(",\t%s\t%s\n",$1,$2); }' vi.out.1 > table_PTLST_INFO.sql
$ ls -t
table_PTLST_INFO.sql
vi.out.1
desc01.out
$ vi -R -c "set number" table_PTLST_INFO.sql
     1  ,       PROJ_NO VARCHAR2(24) NOT NULL
...
    48  ,       SYS_GB  VARCHAR2(18)

4. Add 'CREATE TABLE (', ')', and '/'

CREATE TABLE ...
( ...
.
.
.
)
/

For example,

$ vi -c "set number" table_PTLST_INFO.sql
     1  DROP TABLE PTLST_INFO
     2  /
     3  CREATE TABLE PTLST_INFO
     4  (       PROJ_NO VARCHAR2(24) NOT NULL
...
    51  ,       SYS_GB  VARCHAR2(18)
    52  )
    53  /
:wq
$ 

5. Create the table

SQL> @(SQL script)

For example,

SQL> connect (username)

SQL> @table_PTLST_INFO
DROP TABLE PTLST_INFO
           *
ERROR at line 1:
ORA-00942: table or view does not exist



Table created.

SQL>