I'm looking for a query allowing to retrieve foreign key infos (each line: referrencing table & field, referrenced table & field) of an entire schema.
I've found this, but does not gives all the info I need: https://stackoverflow.com/questions/4389228/sql-for-oracle-to-check-if-a-constraint-exists
I'm currently working on it and may end up with a solution in the next minutes/hours. But if someone has already a full working solution, I'll be glad to know it :)
-
For SQL Developer, you can find this info in an ERD format in the 'Model' tab on a table (as referenced here. Not useful for a script, but if you just need the info and land here like I did, it may be helpful.SnoringFrog– SnoringFrog2017年03月21日 15:02:38 +00:00Commented Mar 21, 2017 at 15:02
4 Answers 4
After some "reverse-engineering" on the queries made by the Navicat tool when opening the design table window for a table (queries retrieving info about foreign keys show up in the history window), here is a solution:
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 ships with a report that does just this.
It does it for the login schema only, but it's a quick fix to make it go get every single FK in the database - although you might want to omit schemas like 'APEX...' and 'SYS.'
It also omits things like, tables in the recycle bin.
The original report is in the Reports panel, in the data dictionary reports.
Here's the amended query to get ALL the FKs.
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
And here's what that report looks like.
A little bit complicated code which also dump comments on cols (based on Frosty code):
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
;
To make output more readable I use break on TABLE_NAME; in sqlplus (look to my question https://stackoverflow.com/questions/14998296/print-only-first-unique-value-for-column-that-order-by-in-oracle-sqlplus/ ).
UPDATE Simpler query that collects list of tables that have FK reference to given table (useful if you like to clean up constraints after table renames):
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 '/';
Found out that most of these scripts have issues when foreign key constraint references a table with more than one column reference.
Here is the one I use:
SELECT c.constraint_name,
l.table_name,
l.column_name,
r.table_name,
r.column_name
FROM user_constraints c,
user_cons_columns l,
user_cons_columns r
WHERE l.table_name = c.table_name
-- AND c.table_name = :tname
AND c.r_constraint_name = r.constraint_name
AND c.constraint_name = l.constraint_name
AND l.position = r.position