Oracle Create Table AS 和表注释和列注释
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6910255/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Oracle Create Table AS and table comments and column comments
提问by reforrer
Is it possible to create another table as CREATE TABLE AS and also preserve columns' comments ?
是否可以创建另一个表作为 CREATE TABLE AS 并保留列的注释？
CREATE TABLE TABLE1_COPY AS SELECT * FROM TABLE1;
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?
前面的语句不包括列的注释。因此 TABLE1_COPY 没有列的注释。使用 USER_COL_COMMENTS 是否也是在我新创建的表上重现相同评论的唯一方法？
回答by Ian Carpenter
As for DMBS_METADATA.GET_DDL it doesn't seem to genereate COMMENT ON COLUMN statements unless I am missing some properties.
至于 DMBS_METADATA.GET_DDL，它似乎不会生成 COMMENT ON COLUMN 语句，除非我缺少某些属性。
One method is to use dbms_metadata.get_dependent_ddl in combination with dbms_metadata.get_ddl
一种方法是将 dbms_metadata.get_dependent_ddl 与 dbms_metadata.get_ddl 结合使用
Here is an example created using SQL plus:
这是使用 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'
回答by street hawk
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.
这是上述解决方案的简单副本，不同之处在于没有提供 USER。其中 TABLE_NAME 是现有表。
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 -
如果您使用的是“Oracle SQL Developer”，则不需要上述查询，因为您可以直接从“SQL”选项卡本身获取结果查询。步骤是——
- Click on the table you want to copy.
- On the right panel go to 'SQL' tab. You will get the same result.
- 单击要复制的表。
- 在右侧面板上，转到“SQL”选项卡。你会得到同样的结果。
