那就自己写咯…… select get_table_ddl('gr_ylzf_ylj') from dual
擦……create or replace function get_table_ddl(iv_tablename in varchar2) return varchar2 is v_sql varchar2(4000); v_col_list varchar2(4000); begin v_sql := 'create table ' || iv_tablename || '(' || chr(10); for r in (select cname, coltype, case when precision is not null then '(' || precision || ',' || scale || ')' when coltype = 'DATE' then null else '(' || width || ')' end width, nulls from col where tname = upper(iv_tablename) order by colno) loop v_col_list := ',' || v_col_list || r.CNAME || ' ' || r.coltype || r.width || ' ' || r.nulls || chr(10) ; end loop; v_col_list := substr(v_col_list,2); v_sql := v_sql || v_col_list || ')'; return v_sql;end;
还有别的方法么
select get_table_ddl('gr_ylzf_ylj') from dual
擦……create or replace function get_table_ddl(iv_tablename in varchar2) return varchar2 is
v_sql varchar2(4000);
v_col_list varchar2(4000);
begin v_sql := 'create table ' || iv_tablename || '(' || chr(10); for r in (select cname, coltype,
case
when precision is not null then
'(' || precision || ',' || scale || ')'
when coltype = 'DATE' then
null
else
'(' || width || ')'
end width, nulls
from col
where tname = upper(iv_tablename)
order by colno) loop
v_col_list := ',' || v_col_list || r.CNAME || ' ' || r.coltype || r.width || ' ' || r.nulls || chr(10) ;
end loop; v_col_list := substr(v_col_list,2); v_sql := v_sql || v_col_list || ')'; return v_sql;end;
BEGIN
DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'SEGMENT_ATTRIBUTES',FALSE);
DBMS_OUTPUT.PUT_LINE( DBMS_METADATA.GET_DDL('TABLE','EMP'));
DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'DEFAULT');
END;
------------------------------------------
CREATE TABLE "SCOTT"."EMP"
( "EMPNO" NUMBER(4,0),
"ENAME" VARCHAR2(10),
"JOB" VARCHAR2(9),
"MGR" NUMBER(4,0),
"HIREDATE" DATE,
"SAL" NUMBER(7,2),
"COMM" NUMBER(7,2),
"DEPTNO" NUMBER(2,0),
CONSTRAINT "PK_EMP" PRIMARY KEY ("EMPNO") ENABLE,
CONSTRAINT "FK_DEPTNO" FOREIGN KEY ("DEPTNO")
REFERENCES "SCOTT"."DEPT" ("DEPTNO") ENABLE
) ------------------------------------------------------
这个是我能得到的最简单的参数了,只有有没有其他的参数,需要参阅其他文档.
Power Designer 更好点,可以直接从数据库里抽取模型,换个方言直接生成MySQL的了。
2 用程序 :自己写。
http://docs.oracle.com/cd/E11882_01/appdev.112/e25788/d_metada.htm#ARPLS640