业务系统表格调研脚本

1.创建记录表格

CREATE TABLE "MC$MSG"

("OWNER" VARCHAR2(30),

"TABLE_NAME" VARCHAR2(30),

"MSG_ID" NUMBER,

"MSG_NAME" VARCHAR2(200),

"MSG" VARCHAR2(4000)) ;

2.生成打印的脚本内容，红色字体部分可替换

用于指定业务表格所在用户，并生成脚本内容

declare

N NUMBER;

v_indcount number;

v_indlist varchar2(4000);

strings varchar2(4000);

begin

execute immediate 'truncate table mc$msg';

for i in ( select owner,table_name from dba_tables

where owner in ('ZC')

ORDER BY OWNER,TABLE_NAME) LOOP

n:=0;

insert into mc$msg values(i.owner,i.table_name,n,'用户.表格',

'







');

 

n:=n+1;

insert into mc$msg values(i.owner,i.table_name,n,'说明','

');

 

n:=n+1;

strings:='';

for j in ( select b.column_name from dba_cons_columns b

where b.constraint_name=( select a.constraint_name from dba_constraints a

where a.OWNER=i.owner

and a.table_name=i.table_name

and a.constraint_type='P')

order by b.POSITION) loop

strings:=strings||','||j.column_name;

end loop;

strings:=substr(strings,2);

insert into mc$msg values(i.owner,i.table_name,n,'主键','

');

 

n:=n+1;

v_indlist:='

用 户'||i.owner||'表 名'||i.table_name||'

说 明'||'&'||'nbsp;

主 键'||nvl(strings,'&'||'nbsp;')||'



';

for x in (select index_name from dba_indexes where table_owner=i.owner and table_name=i.table_name order by index_name) loop

strings:='';

for y in (select column_name from dba_ind_columns where table_owner=i.owner and table_name=i.table_name and index_name=x.index_name order by column_position) loop

strings:=strings||','||y.column_name;

end loop;

v_indlist:=v_indlist||'

'||'

';

end loop;

v_indlist:=v_indlist||'

索引名索引字段

'||x.index_name||''||substr(strings,2)||'

';

select count(index_name) into v_indcount from dba_indexes where table_owner=i.owner and table_name=i.table_name ;

insert into mc$msg values(i.owner,i.table_name,n,'索引','索 引'||decode(v_indcount,0,'&'||'nbsp;',v_indlist)||'');

 

n:=n+1;

insert into mc$msg values(i.owner,i.table_name,n,'备注','备 注'||'&'||'nbsp;');

 

n:=n+1;

insert into mc$msg values(i.owner,i.table_name,n,'字段','字段名中文名类型可空备注');

 

for j in ( select ''||A.COLUMN_NAME||''||'&'||'nbsp;'||a.DATA_TYPE||'('||a.DATA_LENGTH||')'||''||decode(a.NULLABLE,'Y','--','N','NOT NULL')||''||nvl(b.comments,'&'||'nbsp;')||'' STR

from dba_tab_columns a,dba_col_comments b

where a.TABLE_NAME=b.table_name

and a.COLUMN_NAME=b.column_name

and a.OWNER=b.owner

AND A.OWNER=I.OWNER

and a.table_name=I.TABLE_NAME

ORDER BY A.COLUMN_ID) loop

strings:=j.str;

n:=n+1;

insert into mc$msg values(i.owner,i.table_name,n,'字段定义',strings);

end loop;

n:=n+1;

insert into mc$msg values(i.owner,i.table_name,n,'表格结束','

');

commit;

END LOOP;

--create sql script

--DBMS_OUTPUT.PUT_LINE('set markup html on entmap off') ;

DBMS_OUTPUT.PUT_LINE('set pagesize 0') ;

DBMS_OUTPUT.PUT_LINE('set serveroutput on size 800000') ;

DBMS_OUTPUT.PUT_LINE('set head off') ;

DBMS_OUTPUT.PUT_LINE('set feedback off') ;

DBMS_OUTPUT.PUT_LINE('set echo off') ;

DBMS_OUTPUT.PUT_LINE('set long 9999') ;

DBMS_OUTPUT.PUT_LINE('set linesize 9999') ;

DBMS_OUTPUT.PUT_LINE('col msg format a9999') ;

DBMS_OUTPUT.PUT_LINE('set trimout on') ;

DBMS_OUTPUT.PUT_LINE('set trimspool on') ;

DBMS_OUTPUT.PUT_LINE('spool E:\ZC.htm') ;

 

for i in ( select distinct owner,table_name from mc$msg

ORDER BY OWNER,TABLE_NAME) LOOP

DBMS_OUTPUT.PUT_LINE('exec dbms_output.put_line(chr(13));') ;

DBMS_OUTPUT.PUT_LINE('exec DBMS_OUTPUT.PUT_LINE('''||i.owner||'.'||i.table_name||''');') ;

DBMS_OUTPUT.PUT_LINE('select replace(wm_concat(msg),'','',chr(13)) msg from (select msg from mc$msg where owner='''||i.owner||''' and table_name='''||i.table_name||''' order by msg_id);');

END LOOP;

DBMS_OUTPUT.PUT_LINE('spool off') ;

--create finish

END;

/

 

3.将打印的结果拷贝到文本文件，后缀名改为.sql，红色字体部分可替换

之后再cmd下进入sqlplus，运行此sql脚本即可得到固定格式的数据字典信息

例如拷贝到e盘下，名为zc.sql

在cmd中

sqlplus "/ as sysdba"

SQL>@e:\zc.sql

等待完成即可

4.业务表格调研结果如下：