???????????????
- by Todd Bao
?????????,???????????????????,??????????,???????,??????,?????????????:
SYS@fmw//Scripts> @showfkparent hr employees---------------| ||DEPARTMENT_ID| +>-->HR.DEPARTMENTS.DEPARTMENT_ID| ||JOB_ID | +>-->HR.JOBS.JOB_ID| ||MANAGER_ID | +>-->HR.EMPLOYEES.EMPLOYEE_ID| |---------------
SYS@fmw//Scripts> @showfkparent sh sales------------| ||CHANNEL_ID| +>-->SH.CHANNELS.CHANNEL_ID| ||CUST_ID | +>-->SH.CUSTOMERS.CUST_ID| ||PROD_ID | +>-->SH.PRODUCTS.PROD_ID| ||PROMO_ID | +>-->SH.PROMOTIONS.PROMO_ID| ||TIME_ID | +>-->SH.TIMES.TIME_ID| |------------
????????? ??? 30-08-2012
set echo offset verify offset serveroutput ondefine table_owner='&1'define table_name='&2'declare type info_typ is record (ct varchar2(30),cc varchar2(30),po varchar2(30),pt varchar2(30),pc varchar2(30)); type info_tab_typ is table of info_typ index by pls_integer; info_tab info_tab_typ; max_col_length number := 0;beginwith cons_child as (select owner,constraint_name,table_name, r_owner,r_constraint_name from dba_constraints where constraint_type='R' and owner=upper('&table_owner') and table_name=upper('&table_name')), cons_parent as (select owner,constraint_name,table_name from dba_constraints where (owner,constraint_name) in (select r_owner,r_constraint_name from cons_child))select child.table_name child_table_name, child.column_name child_column_name, parent.owner parent_owner, parent.table_name parent_table_name, parent.column_name parent_column_name bulk collect into info_tabfrom cons_child cc, cons_parent cp, dba_cons_columns parent, dba_cons_columns childwhere cc.owner = child.owner and cc.constraint_name = child.constraint_name and cp.owner = parent.owner and cp.constraint_name = parent.constraint_name and cc.r_owner = cp.owner and cc.r_constraint_name = cp.constraint_name and parent.position = child.positionorder by 2;if (info_tab is not null and info_tab.count >0) then for i in 1..info_tab.count loop if length(info_tab(i).cc) > max_col_length then max_col_length := length(info_tab(i).cc); end if; end loop; dbms_output.put_line(rpad('-',max_col_length+2,'-')); dbms_output.put_line(' '||'|'||rpad(' ',max_col_length,' ')||'|'); for i in 1..info_tab.count loop dbms_output.put('|'||rpad(info_tab(i).cc,max_col_length,' ')||'|'); dbms_output.put_line(' +>-->'||info_tab(i).po||'.'||info_tab(i).pt||'.'||info_tab(i).pc); dbms_output.put_line('|'||rpad(' ',max_col_length,' ')||'|'); end loop; dbms_output.put_line(rpad('-',max_col_length+2,'-'));else dbms_output.put_line('### No foreign key defined on this table! ###');end if;end;/undefine table_ownerundefine table_nameset serveroutput off
Todd
