Liste des clés étrangères et des tables auxquelles elles font référence

Question

J'essaie de trouver une requête qui me renvoie une liste des clés étrangères d'une table, ainsi que les tables et les colonnes auxquelles elles font référence. Je suis à mi-chemin avec

 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'
 

Mais il me reste à savoir quelle table et quelle clé primaire sont référencées par cette clé. Comment pourrais-je l'obtenir?

Answer 1

la clé primaire référencée est décrite dans les colonnes r_owner et r_constraint_name de la table ALL_CONSTRAINTS . Cela vous donnera l'information que vous voulez:

 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
 

Answer 2

Voici un script tout usage que nous utilisons qui a été incroyablement pratique.

Enregistrez-le afin de pouvoir l'exécuter directement (@ fkeys.sql). Il vous permettra d'effectuer une recherche par propriétaire et par table parent ou enfant et d'afficher les relations de clé étrangère. Le script actuel spouleur explicitement sur C: \ SQLRPTS, vous devez donc créer ce dossier ou modifier cette ligne en un élément que vous souhaitez utiliser.

 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 &new_prompt' RIGHT 'PAGE:'FORMAT 999 SQL.PNO SKIP 2

SPOOL C:\SQLRPTS\FKeys_&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('&OWNER_NAME') is null then '%'
                                else upper('&OWNER_NAME') end
     and c.table_name like case when upper('&PARENT_TABLE_NAME') is null then '%'
                                else upper('&PARENT_TABLE_NAME') end
     and b.table_name like case when upper('&CHILD_TABLE_NAME') is null then '%'
                                else upper('&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
 

Answer 3

Cela déplacera la hiérarchie des clés étrangères pour une table et une colonne données et renverra les colonnes de l'enfant et du petit-enfant, ainsi que toutes les tables descendantes. Il utilise des sous-requêtes pour ajouter r_table_name et r_column_name à user_constraints, puis les utilise pour connecter des lignes.

 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;
 

Answer 4

Voici une autre solution. L'utilisation des vues par défaut de sys est tellement lente (environ 10 secondes dans mon cas). C'est beaucoup plus rapide que cela (environ 0,5 seconde).

 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 */;
 

Answer 5

Si vous avez besoin de toutes les clés étrangères de l'utilisateur, utilisez le script suivant

 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'
 

basé sur le code de Vincent Malgrat