List of foreign keys and the tables they reference in Oracle DB

Question

I m trying to find a query which will return me a list of the foreign keys for a table and the tables and columns they reference   I am half way there with   SELECT a table name          a column name          a constraint name          c owner FROM ALL CONS COLUMNS A  ALL CONSTRAINTS C   where A CONSTRAINT NAME   C CONSTRAINT NAME    and a table name  TableName    and C CONSTRAINT TYPE    R    But I still need to know which table and primary key are referenced by this key   How would I get that

User · Answer

In case one wants to create FK constraints from UAT environment table to Live  fire below dynamic query           SELECT  ALTER TABLE    OBJ NAME    ADD CONSTRAINT    CONST NAME        FOREIGN KEY     COALESCE ACOL NAME  COL NAME      REFERENCES     ROBJ NAME         COALESCE RACOL NAME  RCOL NAME         FROM SYS CON  CONST INNER JOIN SYS CDEF  CDEF ON CDEF CON    CONST CON  INNER JOIN SYS CCOL  CCOL ON CCOL CON    CONST CON  INNER JOIN SYS COL  COL  ON  CCOL OBJ    COL OBJ   AND  CCOL INTCOL        COL INTCOL   INNER JOIN SYS OBJ  OBJ ON CCOL OBJ    OBJ OBJ  LEFT JOIN SYS ATTRCOL  ACOL ON  CCOL OBJ    ACOL OBJ   AND  CCOL INTCOL        ACOL INTCOL    INNER JOIN SYS CON  RCONST ON RCONST CON    CDEF RCON  INNER JOIN SYS CCOL  RCCOL ON RCCOL CON    RCONST CON  INNER JOIN SYS COL  RCOL  ON  RCCOL OBJ    RCOL OBJ   AND  RCCOL INTCOL        RCOL INTCOL   INNER JOIN SYS OBJ  ROBJ ON RCCOL OBJ    ROBJ OBJ  LEFT JOIN SYS ATTRCOL  RACOL  ON  RCCOL OBJ    RACOL OBJ   AND      RCCOL INTCOL    RACOL INTCOL    WHERE CONST OWNER    userenv  SCHEMAID   AND RCONST OWNER    userenv  SCHEMAID   AND CDEF TYPE    4  AND OBJ NAME    lt table name gt

User · Answer

My version  in my humble opinion  more readable   SELECT   PARENT TABLE NAME   PARENT TABLE NAME           PARENT CONSTRAINT NAME   PARENT PK CONSTRAINT           - gt                CHILD TABLE NAME   CHILD TABLE NAME           CHILD COLUMN NAME   CHILD COLUMN NAME           CHILD CONSTRAINT NAME   CHILD CONSTRAINT NAME  FROM     ALL CONS COLUMNS   CHILD          ALL CONSTRAINTS   CT          ALL CONSTRAINTS   PARENT WHERE    CHILD OWNER     CT OWNER AND      CT CONSTRAINT TYPE     R  AND      CHILD CONSTRAINT NAME     CT CONSTRAINT NAME  AND      CT R OWNER     PARENT OWNER AND      CT R CONSTRAINT NAME     PARENT CONSTRAINT NAME  AND      CHILD TABLE NAME      table -- table name variable AND      CT OWNER      owner  -- schema variable  could not be needed

User · Answer

If you need all the foreign keys of the user then use the following script  SELECT a constraint name  a table name  a column name   c owner          c pk table name r table name   b column name r column name   FROM user cons columns a   JOIN user constraints c ON a owner   c owner        AND a constraint name   c constraint name   JOIN user constraints c pk ON c r owner   c pk owner        AND c r constraint name   c pk constraint name   JOIN user cons columns b ON C PK owner   b owner        AND  C PK CONSTRAINT NAME   b constraint name AND b POSITION   a POSITION       WHERE c constraint type    R    based on Vincent Malgrat code

User · Answer

SELECT a table name  a column name  a constraint name  c owner          -- referenced pk        c r owner  c pk table name r table name  c pk constraint name r pk   FROM all cons columns a   JOIN all constraints c ON a owner   c owner                         AND a constraint name   c constraint name   JOIN all constraints c pk ON c r owner   c pk owner                            AND c r constraint name   c pk constraint name  WHERE c constraint type    R     AND a table name   TABLE NAME    AND c owner   OWNER NAME

User · Answer

For Load UserTable  List of foreign keys and the tables they reference   WITH  reference view AS       SELECT a owner  a table name  a constraint name  a constraint type               a r owner  a r constraint name  b column name         FROM dba constraints a  dba cons columns b        WHERE            a owner   b owner          AND a constraint name   b constraint name          AND constraint type    R    constraint view AS       SELECT a owner a owner  a table name  a column name  b owner b owner               b constraint name         FROM dba cons columns a  dba constraints b        WHERE a owner   b owner          AND a constraint name   b constraint name          AND b constraint type    P                usertableviewlist AS          select  TABLE NAME  from user tables      SELECT          rv table name FK Table   rv column name FK Column          CV table name PK Table   rv column name PK Column   rv r constraint name Constraint Name    FROM reference view rv  constraint view CV   usertableviewlist UTable  WHERE rv r constraint name   CV constraint name AND rv r owner   CV b owner And UTable TABLE NAME   rv table name

User · Answer

select d table name          d constraint name  Primary Constraint Name           b constraint name  Referenced Constraint Name   from user constraints d         select c constraint name                c r constraint name                c table name        from user constraints c         where table name  EMPLOYEES  --your table name instead of EMPLOYEES        and constraint type  R   b  where d constraint name b r constraint name

User · Answer

This will travel the hierarchy of foreign keys for a given table and column and return columns from child and grandchild  and all descendant tables  It uses sub-queries to add r table name and r column name to user constraints  and then uses them to connect rows    select distinct table name  constraint name  column name  r table name  position  constraint type  from       SELECT uc table name       uc constraint name       cols column name        select table name from user constraints where constraint name   uc r constraint name           r table name       select column name from user cons columns where constraint name   uc r constraint name and position   cols position           r column name      cols position      uc constraint type     FROM user constraints uc     inner join user cons columns cols on uc constraint name   cols constraint name      where constraint type     C     start with table name    MY TABLE NAME  and column name    MY COLUMN NAME    connect by nocycle  prior table name   r table name  and prior column name   r column name

User · Answer

Try this   select   from all constraints where r constraint name in  select constraint name  from all constraints where table name  YOUR TABLE NAME

User · Answer

The referenced primary key is described in the columns r owner and r constraint name of the table ALL CONSTRAINTS  This will give you the info you want   SELECT a table name  a column name  a constraint name  c owner          -- referenced pk        c r owner  c pk table name r table name  c pk constraint name r pk   FROM all cons columns a   JOIN all constraints c ON a owner   c owner                         AND a constraint name   c constraint name   JOIN all constraints c pk ON c r owner   c pk owner                            AND c r constraint name   c pk constraint name  WHERE c constraint type    R     AND a table name    TableName

User · Answer

WITH reference view AS       SELECT a owner  a table name  a constraint name  a constraint type               a r owner  a r constraint name  b column name         FROM dba constraints a  dba cons columns b        WHERE  a owner LIKE UPPER   SYS   AND           a owner   b owner          AND a constraint name   b constraint name          AND constraint type    R         constraint view AS       SELECT a owner a owner  a table name  a column name  b owner b owner               b constraint name         FROM dba cons columns a  dba constraints b        WHERE a owner   b owner          AND a constraint name   b constraint name          AND b constraint type    P           AND a owner LIKE UPPER   SYS              SELECT          rv table name FK Table   rv column name FK Column          CV table name PK Table   rv column name PK Column   rv r constraint name Constraint Name    FROM reference view rv  constraint view CV  WHERE rv r constraint name   CV constraint name AND rv r owner   CV b owner

User · Answer

Here is an all-purpose script we use that has been incredibly handy     Save it off so you can execute it directly   fkeys sql    It will let you search by Owner and either the Parent or Child table and show foreign key relationships   The current script does explicitly spool to C  SQLRPTS so you will need to create that folder of change that line to something you want to use   REM                                                                          REM    REM      fkeys sql REM    REM      Displays the foreign key relationships REM    REM                                                                          CLEAR BREAK CLEAR COL SET LINES 200 SET PAGES 54 SET NEWPAGE 0 SET WRAP OFF SET VERIFY OFF SET FEEDBACK OFF  break on table name skip 2 on constraint name on r table name skip 1  column CHILDCOL format a60 head  CHILD COLUMN  column PARENTCOL format a60 head  PARENT COLUMN  column constraint name format a30 head  FK CONSTRAINT NAME  column delete rule format a15 column bt noprint column bo noprint  TTITLE LEFT  DATE CENTER  FOREIGN KEY RELATIONSHIPS ON  amp new prompt  RIGHT  PAGE  FORMAT 999 SQL PNO SKIP 2  SPOOL C  SQLRPTS FKeys  amp new prompt ACCEPT OWNER NAME PROMPT  Enter Table Owner  or blank for all     ACCEPT PARENT TABLE NAME PROMPT  Enter Parent Table or leave blank for all    ACCEPT CHILD TABLE NAME PROMPT  Enter Child Table or leave blank for all       select b owner           b table name           b column name CHILDCOL           b position           c owner           c table name           c column name PARENTCOL           a constraint name           a delete rule           b table name bt           b owner bo     from all cons columns b           all cons columns c           all constraints a    where b constraint name   a constraint name      and a owner             b owner      and b position          c position      and c constraint name   a r constraint name      and c owner             a r owner      and a constraint type    R       and c owner      like case when upper   amp OWNER NAME   is null then                                     else upper   amp OWNER NAME   end      and c table name like case when upper   amp PARENT TABLE NAME   is null then                                     else upper   amp PARENT TABLE NAME   end      and b table name like case when upper   amp CHILD TABLE NAME   is null then                                     else upper   amp CHILD TABLE NAME   end order by 7 6 4 2   SPOOL OFF TTITLE OFF SET FEEDBACK ON SET VERIFY ON CLEAR BREAK CLEAR COL SET PAGES 24 SET LINES 100 SET NEWPAGE 1 UNDEF OWNER

User · Answer

I used below code and it served my purpose-  SELECT fk owner  fk table name  col column name FROM dba constraints pk  dba constraints fk  dba cons columns col WHERE pk constraint name   fk r constraint name AND fk constraint name   col constraint name AND pk owner   col owner AND pk owner   fk owner AND fk constraint type    R     AND pk owner   sys context  USERENV    CURRENT SCHEMA    AND pk table name    my table AND pk constraint type    P

User · Answer

Here is an another solution  Using sys s default views are so slow  approx 10s in my situation   This is much faster than that  approx  0 5s    SELECT     CONST NAME AS CONSTRAINT NAME      RCONST NAME AS REF CONSTRAINT NAME       OBJ NAME AS TABLE NAME      COALESCE ACOL NAME  COL NAME  AS COLUMN NAME      CCOL POS  AS POSITION       ROBJ NAME AS REF TABLE NAME      COALESCE RACOL NAME  RCOL NAME  AS REF COLUMN NAME      RCCOL POS  AS REF POSITION FROM SYS CON  CONST INNER JOIN SYS CDEF  CDEF ON CDEF CON    CONST CON  INNER JOIN SYS CCOL  CCOL ON CCOL CON    CONST CON  INNER JOIN SYS COL  COL  ON  CCOL OBJ    COL OBJ   AND  CCOL INTCOL    COL INTCOL   INNER JOIN SYS OBJ  OBJ ON CCOL OBJ    OBJ OBJ  LEFT JOIN SYS ATTRCOL  ACOL ON  CCOL OBJ    ACOL OBJ   AND  CCOL INTCOL    ACOL INTCOL    INNER JOIN SYS CON  RCONST ON RCONST CON    CDEF RCON  INNER JOIN SYS CCOL  RCCOL ON RCCOL CON    RCONST CON  INNER JOIN SYS COL  RCOL  ON  RCCOL OBJ    RCOL OBJ   AND  RCCOL INTCOL    RCOL INTCOL   INNER JOIN SYS OBJ  ROBJ ON RCCOL OBJ    ROBJ OBJ  LEFT JOIN SYS ATTRCOL  RACOL  ON  RCCOL OBJ    RACOL OBJ   AND  RCCOL INTCOL    RACOL INTCOL    WHERE CONST OWNER    userenv  SCHEMAID     AND RCONST OWNER    userenv  SCHEMAID     AND CDEF TYPE    4      R  Referential Foreign Key

User · Answer

Its a bit late to anwser  but I hope my answer been useful for someone  who needs to select Composite foreign keys    SELECT      C   CONSTRAINT NAME        C   OWNER  AS  SCHEMA NAME        C   TABLE NAME        COL   COLUMN NAME        REF COL   OWNER  AS  REF SCHEMA NAME        REF COL   TABLE NAME  AS  REF TABLE NAME        REF COL   COLUMN NAME  AS  REF COLUMN NAME  FROM      USER CONSTRAINTS   C  INNER JOIN  USER CONS COLUMNS   COL  ON  COL   OWNER     C   OWNER   AND  COL   CONSTRAINT NAME     C   CONSTRAINT NAME  INNER JOIN  USER CONS COLUMNS   REF COL  ON  REF COL   OWNER     C   R OWNER   AND  REF COL   CONSTRAINT NAME     C   R CONSTRAINT NAME   AND  REF COL   POSITION     COL   POSITION  WHERE  C   TABLE NAME     TableName  AND  C   CONSTRAINT TYPE     R

User · Answer

I know it s kinda late to answer but let me answer anyway  some of the answers above are quite complicated hence here is a much simpler take      SELECT a table name child table  a column name child column  a constraint name         b table name parent table  b column name parent column   FROM all cons columns a   JOIN all constraints c ON a owner   c owner AND a constraint name   c constraint name  join all cons columns b on c owner   b owner and c r constraint name   b constraint name  WHERE c constraint type    R     AND a table name    your table name

[oracle] List of foreign keys and the tables they reference in Oracle DB

Examples related to oracle

Examples related to metadata

Examples related to database-metadata