（Oracle）导出表结构

小程序：扫一扫查出行
【扫一扫了解最新限行尾号】
复制小程序

DECLARE
cursor t_name is
SELECT rank() over(order by a.TABLE_NAME) as xiaolonglong,a.TABLE_NAME
FROM User_Tables a where a.TABLE_NAME not like 'TMP%' order by a.table_name; v_sql varchar(5000);
v_result varchar(5000); begin
for i in t_name Loop
v_sql:='SELECT -1,''表3-'||i.xiaolonglong||' '||i.TABLE_NAME||''' from dual
union all
select 0,''序号'''||'||chr(9)||''参数名称'''||'||chr(9)||''参数类型'''||'||chr(9)||''含义'''||'||chr(9)||''长度'''||'||chr(9)||''空/非空'' from dual
union all
SELECT * from(select
T1.Column_Id,
T1.Column_Id ||chr(9)||
trim(T1.COLUMN_NAME) ||chr(9)||
trim(T1.DATA_TYPE) ||chr(9)||
replace(T2.COMMENTS,chr(10),'''') ||chr(9)||
trim(T1.DATA_LENGTH)||chr(9)||
case when t1.nullable=''Y'' then ''允许空'' else ''非空'' end
FROM
USER_TAB_COLS T1, USER_COL_COMMENTS T2
WHERE
T1.TABLE_NAME = T2.TABLE_NAME AND T1.COLUMN_NAME = T2.COLUMN_NAME
AND t1.TABLE_NAME='''||i.TABLE_NAME||''' order by 1) union all';
--execute immediate (v_sql);
--commit;
dbms_output.put_line(v_sql);
END LOOP;
end;
以上语句生成每个表的语句SQL，拷贝出来，在打开新的SQL windows执行。