Oracle get the Primary and foreign Key Column

获取单个表主键
SELECT cols.table_name,cols.column_name,cols.position,cons.status,cons.owner FROM all_constraints cons,all_cons_columns cols WHERE cols.table_name = 'TABLE_NAME' AND cons.constraint_type = 'P' AND cons.constraint_name = cols.constraint_name AND cons.owner = cols.owner ORDER BY cols.table_name,cols.position;

http://stackoverflow.com/questions/9016578/how-to-get-primary-key-column-in-oracle

获取单个表主键外键
SELECT ac.table_name,column_name,position,ac.constraint_name,DECODE (constraint_type,'P','Primary Key','Foreign Key') key_type,(SELECT ac2.table_name FROM all_constraints ac2 WHERE AC2.CONSTRAINT_NAME = AC.R_CONSTRAINT_NAME) fK_to_table FROM all_cons_columns acc,all_constraints ac WHERE acc.constraint_name = ac.constraint_name AND acc.table_name = ac.table_name AND CONSTRAINT_TYPE IN ('P','R') AND ac.table_name = --(your table here) ORDER BY table_name,constraint_type,position;

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 FOR TABLE_NAME_RECORDER IN (SELECT table_name FROM user_tables) LOOP FOR L_RECORD IN (SELECT ac.table_name,column_name FROM all_cons_columns acc,'R') AND ac.table_name = TABLE_NAME_RECORDER.table_name) LOOP --dbms_output.put_line(L_RECORD.column_name);
                   SELECT TABLE_NAME,COLUMN_NAME,DATA_TYPE into alter_table_name,alter_column_name,dtype from all_tab_columns WHERE table_name = L_RECORD.table_name AND column_name = L_RECORD.column_name;
                   IF dtype = 'NUMBER' then
                     sql_stmt := 'ALTER table ' || '"' || alter_table_name ||'"' || ' modify (' || alter_column_name ||' NUMBER(38))'; dbms_output.put_line(sql_stmt||';');
                     execute immediate sql_stmt;
                   END IF;
           END LOOP;
      END LOOP;
   END;

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!