Oracle get the Primary and foreign Key Column

获取单个表主键SELECT cols.table_name, cols.column_name, cols.position, cons.status, cons.ownerFROM all_constraints cons, all_cons_columns colsWHERE cols.table_name = 'TABLE_NAME'AND cons.constraint_type = 'P'AND cons.constraint_name = cols.constraint_nameAND cons.owner = cols.ownerORDER 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, 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 = 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;