List tables that has foreign key to given table.
--- a/oracle.rst Sat Jan 06 01:17:28 2018 +0200
+++ b/oracle.rst Mon Jan 22 19:15:18 2018 +0200
@@ -155,6 +155,16 @@
) and OWNER in ('USER1', 'USER2')
order by OWNER, TABLE_NAME;
+List tables that has foreign key to given table::
+
+ 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 '/';
+
List of current constraints limited to current user::
select * from USER_CONSTRAINTS;