전체 스키마의 외래 키 정보 (각 라인 : 참조 테이블 및 필드, 참조 테이블 및 필드)를 검색 할 수있는 쿼리를 찾고 있습니다.
나는 이것을 찾았지만 필요한 모든 정보를 제공하지는 않는다 : https :
//.com/questions/4389228/sql-for-oracle-to-check-if-a-constraint-exists
나는 현재 그 일을하고 있으며 몇 분 안에 몇 시간 안에 해결책을 얻을 수 있습니다. 그러나 누군가가 이미 완전한 작업 솔루션을 가지고 있다면 그것을 알게되어 기쁩니다. 🙂
답변
테이블에 대한 디자인 테이블 창을 열 때 Navicat 도구 에서 수행 한 쿼리에 대한 일부 “역 엔지니어링”후 ( 이력 창 에 외래 키에 대한 정보를 검색하는 쿼리가 표시됨) 다음은 해결책입니다.
SELECT
CONS.CONSTRAINT_NAME,
CONS.TABLE_NAME,
COLS.COLUMN_NAME,
CONS.R_CONSTRAINT_NAME,
CONS_R.TABLE_NAME R_TABLE_NAME,
COLS_R.COLUMN_NAME R_COLUMN_NAME
FROM USER_CONSTRAINTS CONS
LEFT JOIN USER_CONS_COLUMNS COLS ON COLS.CONSTRAINT_NAME = CONS.CONSTRAINT_NAME
LEFT JOIN USER_CONSTRAINTS CONS_R ON CONS_R.CONSTRAINT_NAME = CONS.R_CONSTRAINT_NAME
LEFT JOIN USER_CONS_COLUMNS COLS_R ON COLS_R.CONSTRAINT_NAME = CONS.R_CONSTRAINT_NAME
-- returns only foreign key constraints
WHERE CONS.CONSTRAINT_TYPE = 'R'
ORDER BY CONS.TABLE_NAME, COLS.COLUMN_NAME답변
SQL Developer는이를 수행하는 보고서와 함께 제공됩니다.
로그인 스키마에만 적용되지만 ‘APEX …’및 ‘SYS’와 같은 스키마는 생략 할 수 있지만 데이터베이스에서 모든 단일 FK를 가져 오도록 신속하게 수정합니다.
또한 휴지통의 테이블과 같은 것을 생략합니다.
원본 보고서는 보고서 패널의 데이터 사전 보고서에 있습니다.
다음은 모든 FK를 가져 오는 수정 된 쿼리입니다.
SELECT
c.owner "Owner",
c.table_name "Table_Name",
c.constraint_name "Constraint_Name",
c.delete_rule "Delete_Rule",
d.columns,
c.r_owner "Owner of Related Table",
(
SELECT
r.table_name
FROM
sys.all_constraints r
WHERE
c.r_owner = r.owner
AND
c.r_constraint_name = r.constraint_name
) "Related Table",
c.r_constraint_name "Related Constraint"
FROM
sys.all_constraints c,
(
SELECT
a.owner,
a.table_name,
a.constraint_name,
MAX(
DECODE(position,1,substr(column_name,1,30),NULL)
)
|| MAX(
DECODE(position,2,','
|| substr(column_name,1,30),NULL)
)
|| MAX(
DECODE(position,3,','
|| substr(column_name,1,30),NULL)
)
|| MAX(
DECODE(position,4,','
|| substr(column_name,1,30),NULL)
)
|| MAX(
DECODE(position,5,','
|| substr(column_name,1,30),NULL)
)
|| MAX(
DECODE(position,6,','
|| substr(column_name,1,30),NULL)
)
|| MAX(
DECODE(position,7,','
|| substr(column_name,1,30),NULL)
)
|| MAX(
DECODE(position,8,','
|| substr(column_name,1,30),NULL)
)
|| MAX(
DECODE(position,9,','
|| substr(column_name,1,30),NULL)
)
|| MAX(
DECODE(position,10,','
|| substr(column_name,1,30),NULL)
)
|| MAX(
DECODE(position,11,','
|| substr(column_name,1,30),NULL)
)
|| MAX(
DECODE(position,12,','
|| substr(column_name,1,30),NULL)
)
|| MAX(
DECODE(position,13,','
|| substr(column_name,1,30),NULL)
)
|| MAX(
DECODE(position,14,','
|| substr(column_name,1,30),NULL)
)
|| MAX(
DECODE(position,15,','
|| substr(column_name,1,30),NULL)
)
|| MAX(
DECODE(position,16,','
|| substr(column_name,1,30),NULL)
) columns
FROM
sys.all_constraints a,
sys.all_cons_columns b
WHERE
a.constraint_name = b.constraint_name
AND
a.owner = b.owner
AND
a.constraint_type = 'R'
AND
substr(a.table_name,1,4) != 'BIN$'
AND
substr(a.table_name,1,3) != 'DR$'
AND (
:table_name IS NULL
OR
instr(upper(a.table_name),upper(:table_name) ) > 0
) GROUP BY
a.owner,
a.table_name,
a.constraint_name
) d
WHERE
c.owner = d.owner
AND
c.table_name = d.table_name
AND
c.constraint_name = d.constraint_name
ORDER BY
c.owner,
c.table_name,
c.constraint_name그 보고서는 다음과 같습니다.
답변
cols에 대한 주석을 덤프하는 약간 복잡한 코드 (Frosy 코드 기반) :
SELECT
dt.table_name, dt.column_name, dt.data_type, dt.data_length,
constr.r_tbl r_table, constr.r_col r_column,
comm.comments
FROM user_col_comments comm, user_tab_columns dt
LEFT OUTER JOIN (
SELECT
cons.table_name tbl,
cols.column_name col,
cons_r.table_name r_tbl,
cols_r.column_name r_col
FROM user_constraints cons
LEFT JOIN user_cons_columns cols ON cols.constraint_name = cons.constraint_name
LEFT JOIN user_constraints cons_r ON cons_r.constraint_name = cons.r_constraint_name
LEFT JOIN user_cons_columns cols_r ON cols_r.constraint_name = cons.r_constraint_name
WHERE cons.constraint_type = 'R'
) constr ON constr.tbl = dt.table_name AND constr.col = dt.column_name
WHERE dt.table_name = comm.table_name
AND dt.column_name = comm.column_name
ORDER BY dt.table_name, dt.column_name
;출력보다 읽기 I 사용을 확인하려면 break on TABLE_NAME;에 sqlplus내 질문에 (보기 /programming/14998296/print-only-first-unique-value-for-column-that-order-by-in-oracle-sqlplus / ).
UPDATE 주어진 테이블에 대한 FK 참조가있는 테이블 목록을 수집하는 더 간단한 쿼리 (테이블 이름을 바꾼 후 제약 조건을 정리하려는 경우 유용) :
select * from SYS.USER_CONSTRAINTS cons
join SYS.USER_CONSTRAINTS rcons on rcons.CONSTRAINT_NAME = cons.R_CONSTRAINT_NAME
where cons.CONSTRAINT_TYPE = 'R' and rcons.TABLE_NAME 'TBL_NAME';
select * from SYS.USER_CONSTRAINTS cons
join SYS.USER_CONSTRAINTS rcons on rcons.CONSTRAINT_NAME = cons.R_CONSTRAINT_NAME
where cons.CONSTRAINT_TYPE = 'R' and rcons.TABLE_NAME like '%/_OLD' escape '/';