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

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 les informations 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 

Essaye ça:

 select * from all_constraints where r_constraint_name in (select constraint_name from all_constraints where table_name='YOUR_TABLE_NAME'); 

Voici un script polyvalent que nous utilisons et qui est incroyablement pratique.

Enregistrez-le pour pouvoir l’exécuter directement (@ fkeys.sql). Il vous permettra de rechercher par propriétaire et la table parent ou enfant et afficher les relations de clé étrangère. Le script actuel est explicitement spoulé dans C: \ SQLRPTS. Vous devrez donc créer ce dossier de modification de cette ligne en fonction de ce 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 

Cela va parcourir la hiérarchie des clés étrangères pour une table et une colonne données et renvoyer des colonnes de l’enfant et du petit-enfant, et toutes les tables descendantes. Il utilise des sous-requêtes pour append 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; 

Voici une autre solution. L’utilisation des vues par défaut de sys est tellement lente (environ 10 secondes dans ma situation). C’est beaucoup plus rapide que ça (env. 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 */; 

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

Je sais que c’est un peu tard pour répondre, mais laissez-moi répondre quand même, certaines des réponses ci-dessus sont assez compliquées, d’où une prise beaucoup plus simple.

 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' 

Dans le cas où l’on veut créer des contraintes FK depuis la table d’environnement UAT vers Live, déclenchez une requête dynamic …..

  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 = ; 

Ma version, à mon humble avis, plus lisible:

 SELECT PARENT.TABLE_NAME "PARENT TABLE_NAME" , PARENT.CONSTRAINT_NAME "PARENT PK CONSTRAINT" , '->' " " , 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 

Il est un peu tard pour répondre, mais j’espère que ma réponse a été utile pour quelqu’un qui doit sélectionner des clés étrangères composites.

 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' 

J’ai utilisé le code ci-dessous et cela a servi mon but-

 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'; 

 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 

 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; 

 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; 

Pour Load UserTable (Liste des clés étrangères et des tables référencées)

 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;