Getting the List of Referring Tables to a Table

If you wanna know, in Oracle, all referring tables to a certain table here is a simple query:

SELECT  bs.OWNER , bs.TABLE_NAME , bs.CONSTRAINT_NAMEFROM  USER_CONSTRAINTS bs , USER_CONSTRAINTS rf WHERE  bs.CONSTRAINT_TYPE='R'  AND rf.TABLE_NAME='YOUR_TABLE_NAME' AND bs.R_CONSTRAINT_NAME=rf.CONSTRAINT_NAME;

If, in addition, you wanna also know the referring column names:

SELECT  bs.OWNER , bs.TABLE_NAME , cl.COLUMN_NAME , bs.CONSTRAINT_NAMEFROM  USER_CONSTRAINTS bs , USER_CONSTRAINTS rf  , USER_CONS_COLUMNS clWHERE  bs.CONSTRAINT_TYPE='R'  AND rf.TABLE_NAME='T_CL_KUNDE_D' AND bs.R_CONSTRAINT_NAME=rf.CONSTRAINT_NAME AND bs.CONSTRAINT_NAME=cl.CONSTRAINT_NAME;

Using the same script of the previous post, we can implement a fast script for disabling all the foreign key contraints referring to the desired table:

SET SERVEROUTPUT ON;DECLARE v_stmt VARCHAR2(255);BEGIN
  FOR i IN
(
SELECT
bs.CONSTRAINT_NAME "constraint_name"
, bs.TABLE_NAME "table_name"
FROM
USER_CONSTRAINTS bs
, USER_CONSTRAINTS rf
, USER_CONS_COLUMNS cl
WHERE
bs.CONSTRAINT_TYPE='R'
AND rf.TABLE_NAME='your_table_name'
AND bs.R_CONSTRAINT_NAME=rf.CONSTRAINT_NAME
AND bs.CONSTRAINT_NAME=cl.CONSTRAINT_NAME
)
  LOOP
   v_stmt := 'ALTER TABLE your_table_name DISABLE CONSTRAINT ' || i.CONSTRAINT_NAME;
   EXECUTE IMMEDIATE v_stmt;

   dbms_output.put_line('disabled: ' || i.constraint_name || ' from table: ' ||  i.table_name);
  END LOOP;
END;
/