業務系統表格調研指令碼

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.業務表格調研結果如下：