디비랑[dɪ'bɪraŋ]

/********************************************************************************************
-- Title : [10g] 특정 테이블을 참조한 테이블 찾기
-- Reference : dbrang.com
-- Key word : 참조테이블 참조 테이블 reference foreign
********************************************************************************************/

-- 테이블 삭제
DROP TABLE scott.ta;
DROP TABLE scott.tb;
DROP TABLE scott.tc;

-- 테이블 생성
CREATE TABLE scott.ta
( a NUMBER NOT NULL, b VARCHAR2(5)
, CONSTRAINT pk_a PRIMARY KEY (a));

CREATE TABLE scott.tb
( a NUMBER NOT NULL, ba VARCHAR2(5)
, CONSTRAINT pk_b PRIMARY KEY (a)
, CONSTRAINT fk_ba FOREIGN KEY (a) REFERENCES scott.ta(a));

CREATE TABLE scott.tc
( a NUMBER NOT NULL, ca VARCHAR2(5)
, CONSTRAINT fk_ca FOREIGN KEY (a) REFERENCES scott.ta(a));

-- scott.ta를 참조한 테이블 조회
SELECT b.constraint_name, b.owner || '.' || b.table_name "FK_TABLE"
     , a.owner || '.' || a.table_name "PK_TABLE"
FROM dba_constraints a
INNER JOIN
(
    SELECT *
    FROM dba_constraints
    WHERE constraint_type = 'R'
) b
ON a.owner = b.r_owner
AND a.constraint_name = b.r_constraint_name
WHERE a.owner = 'SCOTT'   -- 이 아래 주석처리하면 모든 테이블 출력
AND a.table_name = 'TA'