oracle 创建表 as,sql – Oracle创建表AS和表注释和列注释

As for DMBS_METADATA.GET_DDL it doesn’t seem to genereate COMMENT ON

COLUMN statements unless I am missing some properties.

一种方法是将dbms_metadata.get_dependent_ddl与dbms_metadata.get_ddl结合使用

以下是使用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'