6

Is it possible to create another table as CREATE TABLE AS and also preserve columns' comments ?


CREATE TABLE TABLE1_COPY AS SELECT * FROM TABLE1;

The previous statement does not include columns' comments. Therefore TABLE1_COPY is left without columns' comments. Is using USER_COL_COMMENTS the only way to reproduce the same comments on my newly created table too?

Komal12
  • 3,340
  • 4
  • 16
  • 25
reforrer
  • 735
  • 7
  • 13
  • 18
  • see: http://stackoverflow.com/questions/233870/how-can-i-create-a-copy-of-an-oracle-table-without-copying-the-data looks similar and has the answer too – Kevin Burton Aug 02 '11 at 10:29
  • As for DMBS_METADATA.GET_DDL it doesn't seem to genereate COMMENT ON COLUMN statements unless I am missing some properties. – reforrer Aug 02 '11 at 10:56
  • 5
    Well, the proper way to do this would be to go to your *source control repository*, take a copy of the DDL script for TABLE1 and edit that copy. – APC Aug 02 '11 at 11:35

2 Answers2

15

As for DMBS_METADATA.GET_DDL it doesn't seem to genereate COMMENT ON COLUMN statements unless I am missing some properties.

One method is to use dbms_metadata.get_dependent_ddl in combination with dbms_metadata.get_ddl

Here is an example created using SQL plus:

SQL> set long 1000000

SQL> create table t (x number);

Table created.

SQL> comment on column T.X IS 'this is the column comment';

Comment created.

SQL> comment on table T IS 'this is the table comment';

Comment created.

SQL> SELECT dbms_metadata.get_ddl( 'TABLE', 'T' ) || ' ' ||
  2         dbms_metadata.get_dependent_ddl( 'COMMENT', 'T', USER ) the_ddl
  3  FROM dual
  4  /

THE_DDL
--------------------------------------------------------------------------------

  CREATE TABLE "SCOTT"."T"
   (    "X" NUMBER
   ) SEGMENT CREATION DEFERRED
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  TABLESPACE "USERS"

   COMMENT ON COLUMN "SCOTT"."T"."X" IS 'this is the column comment'

   COMMENT ON TABLE "SCOTT"."T"  IS 'this is the table comment'
Ian Carpenter
  • 8,346
  • 6
  • 50
  • 82
0

This is a copy of the above solution in a simple way, the difference is there is no USER provided. Where TABLE_NAME is an existing table.

SELECT dbms_metadata.get_ddl( 'TABLE','TABLE_NAME' ) || ' ' || dbms_metadata.get_dependent_ddl( 'COMMENT', 'TABLE_NAME' ) the_ddl FROM dual;

If you are using 'Oracle SQL Developer', the above query is not necessary because you can directly get the result query from the 'SQL' tab itself. The steps are -

  1. Click on the table you want to copy.
  2. On the right panel go to 'SQL' tab. You will get the same result.
street hawk
  • 565
  • 7
  • 12