CREATE TABLE "T" ( "A" VARCHAR2(1 CHAR) NOT NULL, "B" INT, CONSTRAINT PK_T PRIMARY KEY ("A") );COMMENT ON TABLE "T" IS 't';COMMENT ON COLUMN "T"."A" IS 'a';COMMENT ON COLUMN "T"."B" IS 'b';
你在“企业管理器”里把“显示SQL”功能打开不就成了
你如果需要图形化工具来帮助你完成这件事,推荐toad 8.5.3
利用pl/sql也能查看到建表的sql
--get the table DDL CREATE OR REPLACE FUNCTION get_table_md (schemaName in VARCHAR2, tableName in VARCHAR2) RETURN CLOB IS -- Define local variables. h NUMBER; --handle returned by OPEN th NUMBER; -- handle returned by ADD_TRANSFORM doc CLOB; BEGIN-- Specify the object type. h := DBMS_METADATA.OPEN('TABLE');-- Use filters to specify the particular object desired. --schemaName := upper(schemaName); --tableName := upper(tableName); DBMS_METADATA.SET_FILTER(h,'SCHEMA',upper(schemaName)); DBMS_METADATA.SET_FILTER(h,'NAME',upper(tableName)); -- Request that the metadata be transformed into creation DDL. th := DBMS_METADATA.ADD_TRANSFORM(h,'DDL'); -- Fetch the object. doc := DBMS_METADATA.FETCH_CLOB(h); -- Release resources. DBMS_METADATA.CLOSE(h); RETURN doc; END; /
这样调用 select get_table_md('owner','table_name') from dual; 但是得不到关于该表的约束、索引等信息
用pl/sql shift+左件点表名称,出来个东西,点右下的显示sql
你用:select dbms_metadata.get_ddl('object_type','table_name','table_owner') from dual 就解决了。 比如:select dbms_metadata.get_ddl('TABLE','TEST','AAA') from dual
"A" VARCHAR2(1 CHAR) NOT NULL,
"B" INT,
CONSTRAINT PK_T PRIMARY KEY ("A")
);COMMENT ON TABLE "T" IS
't';COMMENT ON COLUMN "T"."A" IS
'a';COMMENT ON COLUMN "T"."B" IS
'b';
CREATE OR REPLACE FUNCTION get_table_md (schemaName in VARCHAR2, tableName in VARCHAR2)
RETURN CLOB IS
-- Define local variables.
h NUMBER; --handle returned by OPEN
th NUMBER; -- handle returned by ADD_TRANSFORM
doc CLOB;
BEGIN-- Specify the object type.
h := DBMS_METADATA.OPEN('TABLE');-- Use filters to specify the particular object desired.
--schemaName := upper(schemaName);
--tableName := upper(tableName);
DBMS_METADATA.SET_FILTER(h,'SCHEMA',upper(schemaName));
DBMS_METADATA.SET_FILTER(h,'NAME',upper(tableName)); -- Request that the metadata be transformed into creation DDL.
th := DBMS_METADATA.ADD_TRANSFORM(h,'DDL'); -- Fetch the object.
doc := DBMS_METADATA.FETCH_CLOB(h); -- Release resources.
DBMS_METADATA.CLOSE(h);
RETURN doc;
END;
/
呵呵,写NameSpace就可以了。就是一个名字空间下面有若干个表。
select get_table_md('owner','table_name') from dual;
但是得不到关于该表的约束、索引等信息
shift+左件点表名称,出来个东西,点右下的显示sql
就解决了。
比如:select dbms_metadata.get_ddl('TABLE','TEST','AAA') from dual
例如 toad
http://bbs.csai.cn/xch/from.asp?id=29&wh=jimeljm