select decode(a.column_id,1,'create or replace table '||a.table_name||'(',''), a.column_name, a.data_type||decode(a.data_type, 'DATE',decode(a.nullable,'N','not null',''), 'VARCHAR2','('||to_char(a.data_length)||')'||decode(a.nullable,'N',' not null'), 'NUMBER',decode(a.data_precision,null,' ', '('||to_char(a.data_precision)||','||to_char(a.data_scale)||')'), 'CHAR','('||to_char(a.data_length)||')'||decode(a.nullable,'N',' not null') ) ||decode(a.column_id,2,');',',') from user_tab_columns a, user_tab_columns b where 1=1 and a.table_name=b.table_name and a.table_name='你的表名' <---表名 and a.column_id=b.column_idgroup by a.column_id, a.table_name, a.data_type, a.nullable, a.data_length, a.data_scale, a.column_name, a.data_precision
pl/sql工具中在table标签下选中某个表-》edit-》view sql
在Oracle9i以后可以使用 select dbms_metadata.get_ddl('TABLE','TABLE_NAME','OWNER') from dual; 获得dll创建语句.
decode(a.column_id,1,'create or replace table '||a.table_name||'(',''),
a.column_name,
a.data_type||decode(a.data_type,
'DATE',decode(a.nullable,'N','not null',''),
'VARCHAR2','('||to_char(a.data_length)||')'||decode(a.nullable,'N',' not null'),
'NUMBER',decode(a.data_precision,null,' ',
'('||to_char(a.data_precision)||','||to_char(a.data_scale)||')'),
'CHAR','('||to_char(a.data_length)||')'||decode(a.nullable,'N',' not null')
)
||decode(a.column_id,2,');',',')
from
user_tab_columns a,
user_tab_columns b
where
1=1
and a.table_name=b.table_name
and a.table_name='你的表名' <---表名
and a.column_id=b.column_idgroup by
a.column_id,
a.table_name,
a.data_type,
a.nullable,
a.data_length,
a.data_scale,
a.column_name,
a.data_precision
select dbms_metadata.get_ddl('TABLE','TABLE_NAME','OWNER') from dual;
获得dll创建语句.