How to retrieve all table columns, data type, data length, constraint type and referenced column and table

Question

I'm able to list most of the information needed but can't figure out how to list the referenced tables and columns

the information needed are:

1. Table name
2. Column name
3. Data type
4. Constraint type
5. Reference table
6. Reference column

 SELECT
    at.owner,
    at.table_name,
    atc.column_name    AS column_names,
    atc.data_type      AS data_type,
    ac.constraint_type
FROM
         all_tables at
    JOIN all_tab_columns   atc ON at.table_name = atc.table_name
    LEFT JOIN all_cons_columns  acc ON atc.column_name = acc.column_name
                                      AND acc.table_name = at.table_name
    LEFT JOIN all_constraints   ac ON ac.constraint_name = acc.constraint_name
WHERE
    at.owner NOT IN ( 'SYS', 'SYSTEM', 'DBSNMP', 'DVSYS', 'GSMADMIN_INTERNAL',
                      'LBACSYS',
                      'MDSYS',
                      'OJVMSYS',
                      'ORDDATA',
                      'WMSYS',
                      'XDB',
                      'CTXSYS',
                      'APPQOSSYS',
                      'AUDSYS',
                      'OUTLN',
                      'DBSFWUSER',
                      'ORDSYS',
                      'OLAPSYS' )
GROUP BY
    at.owner,
    at.table_name,
    ac.constraint_type,
    atc.column_name,
    atc.data_type,
    ac.r_owner
ORDER BY
    at.owner;

Is there a way I can get all the constraints along with referenced key and table name? I have tried a lot but not able to figure that out.

Answer 1

You'll need outer self join. Look at the following example:

Master table:

SQL> create table master (id_mas number constraint pk_mas primary key);

Table created.

Detail table, whose ID_MAS column references the master table:

SQL> create table detail (id_det number constraint pk_det primary key,
  2                       id_mas number constraint fk_det_mas references master (id_mas));

Table created.

Query that returns info you're looking for. Note outer self join with both USER_CONSTRAINTS and USER_CONS_COLUMNS (lines #8 and 9):

SQL> select mt.table_name,
  2         mtc.column_name,
  3         mt.constraint_type,
  4         --
  5         mtr.table_name ref_table_name,
  6         mtrc.column_name ref_column_name
  7  from user_constraints mt join user_cons_columns mtc on mtc.constraint_name = mt.constraint_name
  8  left join user_constraints mtr on mtr.constraint_name = mt.r_constraint_name
  9  left join user_cons_columns mtrc on mtrc.constraint_name = mtr.constraint_name
 10  where mt.table_name in ('MASTER', 'DETAIL')
 11  order by mt.table_name desc, mt.constraint_type;

TABLE_NAME      COLUMN_NAME     CONSTRAINT_TYPE REF_TABLE_NAME  REF_COLUMN_NAME
--------------- --------------- --------------- --------------- ---------------
MASTER          ID_MAS          P
DETAIL          ID_DET          P
DETAIL          ID_MAS          R               MASTER          ID_MAS

SQL>

The 3rd rows reads as: in detail table, there's the id_mas column which references master table's id_mas column.