获取单个表主键
SELECT@H_502_3@ cols.table_name,cols.column_name,cols.position,cons.status,cons.owner FROM@H_502_3@ all_constraints cons,all_cons_columns cols WHERE@H_502_3@ cols.table_name = @H_301_8@'TABLE_NAME'@H_502_3@ AND@H_502_3@ cons.constraint_type = @H_301_8@'P'@H_502_3@ AND@H_502_3@ cons.constraint_name = cols.constraint_name AND@H_502_3@ cons.owner = cols.owner ORDER@H_502_3@ BY@H_502_3@ cols.table_name,cols.position;@H_502_3@
http://stackoverflow.com/questions/9016578/how-to-get-primary-key-column-in-oracle
获取单个表主键外键
SELECT@H_502_3@ ac.table_name,column_name,position,ac.constraint_name,DECODE (constraint_type,@H_301_8@'P'@H_502_3@,@H_301_8@'Primary Key'@H_502_3@,@H_301_8@'Foreign Key'@H_502_3@) key_type,(SELECT@H_502_3@ ac2.table_name FROM@H_502_3@ all_constraints ac2 WHERE@H_502_3@ AC2.CONSTRAINT_NAME = AC.R_CONSTRAINT_NAME) fK_to_table FROM@H_502_3@ all_cons_columns acc,all_constraints ac WHERE@H_502_3@ acc.constraint_name = ac.constraint_name AND@H_502_3@ acc.table_name = ac.table_name AND@H_502_3@ CONSTRAINT_TYPE IN@H_502_3@ (@H_301_8@'P'@H_502_3@,@H_301_8@'R'@H_502_3@) AND@H_502_3@ ac.table_name = --(your table@H_502_3@ here) ORDER@H_502_3@ BY@H_502_3@ table_name,constraint_type,position;@H_502_3@
https://community.oracle.com/thread/2182932
获取所有表主键外键,将NUMBER类型的改成38。
DECLARE
alter_table_name varchar2(40);
alter_column_name varchar2(30);
dtype varchar2(10);
sql_stmt VARCHAR2(200);
BEGIN@H_502_3@ FOR@H_502_3@ TABLE_NAME_RECORDER IN@H_502_3@ (SELECT@H_502_3@ table_name FROM@H_502_3@ user_tables) LOOP FOR@H_502_3@ L_RECORD IN@H_502_3@ (SELECT@H_502_3@ ac.table_name,column_name FROM@H_502_3@ all_cons_columns acc,@H_301_8@'R'@H_502_3@) AND@H_502_3@ ac.table_name = TABLE_NAME_RECORDER.table_name) LOOP --dbms_output.put_line(L_RECORD.column_name);@H_502_3@
SELECT@H_502_3@ TABLE_NAME,COLUMN_NAME,DATA_TYPE into@H_502_3@ alter_table_name,alter_column_name,dtype from@H_502_3@ all_tab_columns WHERE@H_502_3@ table_name = L_RECORD.table_name AND@H_502_3@ column_name = L_RECORD.column_name;@H_502_3@
IF dtype = 'NUMBER' then
sql_stmt := 'ALTER@H_502_3@ table@H_502_3@ @H_301_8@' || '@H_502_3@@H_301_8@"' || alter_table_name ||'"@H_502_3@@H_301_8@' || '@H_502_3@ modify (@H_301_8@' || alter_column_name ||'@H_502_3@ NUMBER@H_502_3@(38@H_502_3@))@H_301_8@'; dbms_output.put_line(sql_stmt||'@H_502_3@;@H_502_3@');
execute immediate sql_stmt;
END@H_502_3@ IF@H_502_3@;@H_502_3@
END@H_502_3@ LOOP;@H_502_3@
END@H_502_3@ LOOP;@H_502_3@
END@H_502_3@;@H_502_3@