How to retrieve foreign key constraints data

Question

I'm looking for a query allowing to retrieve foreign key infos (each line: referrencing table & field, referrenced table & field) of an entire schema.

I've found this, but does not gives all the info I need: http://stackoverflow.com/questions/4389228/sql-for-oracle-to-check-if-a-constraint-exists

I'm currently working on it and may end up with a solution in the next minutes/hours. But if someone has already a full working solution, I'll be glad to know it :)

Answer 1

After some "reverse-engineering" on the queries made by the Navicat tool when opening the design table window for a table (queries retrieving info about foreign keys show up in the history window), here is a solution:

SELECT
    CONS.CONSTRAINT_NAME,
    CONS.TABLE_NAME,
    COLS.COLUMN_NAME,
    CONS.R_CONSTRAINT_NAME,
    CONS_R.TABLE_NAME R_TABLE_NAME,
    COLS_R.COLUMN_NAME R_COLUMN_NAME

FROM USER_CONSTRAINTS CONS
    LEFT JOIN USER_CONS_COLUMNS COLS ON COLS.CONSTRAINT_NAME = CONS.CONSTRAINT_NAME
    LEFT JOIN USER_CONSTRAINTS CONS_R ON CONS_R.CONSTRAINT_NAME = CONS.R_CONSTRAINT_NAME
    LEFT JOIN USER_CONS_COLUMNS COLS_R ON COLS_R.CONSTRAINT_NAME = CONS.R_CONSTRAINT_NAME

-- returns only foreign key constraints
WHERE CONS.CONSTRAINT_TYPE = 'R'

ORDER BY CONS.TABLE_NAME, COLS.COLUMN_NAME

Answer 2

A little bit complicated code which also dump comments on cols (based on Frosty code):

SELECT
    dt.table_name, dt.column_name, dt.data_type, dt.data_length,
    constr.r_tbl r_table, constr.r_col r_column,
    comm.comments
  FROM user_col_comments comm, user_tab_columns dt
  LEFT OUTER JOIN (
    SELECT
      cons.table_name tbl,
      cols.column_name col,
      cons_r.table_name r_tbl,
      cols_r.column_name r_col
    FROM user_constraints cons
      LEFT JOIN user_cons_columns cols ON cols.constraint_name = cons.constraint_name
      LEFT JOIN user_constraints cons_r ON cons_r.constraint_name = cons.r_constraint_name
      LEFT JOIN user_cons_columns cols_r ON cols_r.constraint_name = cons.r_constraint_name
    WHERE cons.constraint_type = 'R'
    ) constr ON constr.tbl = dt.table_name AND constr.col = dt.column_name
  WHERE dt.table_name = comm.table_name
    AND dt.column_name = comm.column_name
  ORDER BY dt.table_name, dt.column_name
  ;

To make output more readable I use break on TABLE_NAME; in sqlplus (look to my question http://stackoverflow.com/questions/14998296/print-only-first-unique-value-for-column-that-order-by-in-oracle-sqlplus/ ).