How to find the Foreign keys of all child tables when pass the Parent Table name.

I developed like below.Any experts query ....please send.

select
table_name,constraint_name
from user_cons_columns where column_name=(
select a.COLUMN_NAME from user_cons_columns a,user_constraints b
where a.TABLE_NAME=b.TABLE_NAME
and b.constraint_type in ('P')
and a.TABLE_NAME='DEPT')
and constraint_name like '%FK%'

sheker2007
Profile Answers by sheker2007 Questions by sheker2007
Nov 9th, 2007
4
12635

Questions by sheker2007

Basics

Answer

Showing Answers 1 - 4 of 4 Answers

kishore.giri
Profile Answers by kishore.giri Questions by kishore.giri

Dec 12th, 2007

MY DEAR,

PLZ QUERY THE DATA DICTIONARY VIEW CALLED 'USER_DEPENDENCIES' AND PASS YOUR PARENT TABLENAME IN THE NAME COLUMN AND ALSO GIVE THE TYPE = TABLE IN THE WHERE CLAUSE OF QUERY.

PLZ TRY IT AND REPLY.

REGARDS
Kishore Kumar Giri

Imran_Javed
Profile Answers by Imran_Javed

May 13th, 2008

SELECT
uc.CONSTRAINT_NAME,
ut.TABLE_NAME,
ucc.COLUMN_NAME
FROM
USER_CONS_COLUMNS ucc,
USER_CONSTRAINTS uc ,
USER_TABLES ut
WHERE
uc.table_name = ut.TABLE_NAME AND
uc.CONSTRAINT_NAME =ucc.CONSTRAINT_NAME AND
uc.CONSTRAINT_TYPE = 'R' AND
ut.table_name = 'DEPT';

sen_sam86
Profile Answers by sen_sam86

Aug 18th, 2009

Try this script,

Example

SELECT child.table_name AS "TABL_NAME_2",
'Is Parent Of' AS "RELATION",
parent.table_name AS "TABL_NAME_1",
UCC.column_name AS "COL_NAME",
parent.constraint_name AS "CONSTRNT_NAME",
DECODE (parent.constraint_type, 'P', 'Primary Key', 'R', 'Foreign Key') AS "CONSTRNT_TYPE"
FROM user_constraints parent,
user_constraints child,
user_cons_columns UCC
WHERE parent.r_constraint_name = child.constraint_name
AND parent.r_owner = child.owner
AND child.table_name = v_tab_name
AND child.constraint_name = ucc.constraint_name

UNION ALL

SELECT child.table_name AS "TABL_NAME_2",
'Is Child Of' AS "RELATION",
parent.table_name AS "TABL_NAME_1",
ucc.column_name AS "COL_NAME",
child.constraint_name AS "CONSTRNT_NAME",
DECODE (child.constraint_type, 'P', 'Primary Key', 'R', 'Foreign Key') AS "CONSTRNT_TYPE"
FROM user_constraints child,
user_constraints parent,
user_cons_columns ucc
WHERE child.r_constraint_name = parent.constraint_name
AND child.r_owner = parent.owner
AND child.table_name = v_tab_name
--and child.constraint_type in ('R')
AND child.constraint_name = ucc.constraint_name

Hope it will help you,

asheesh

Jun 2nd, 2012

select table_name from USER_CONSTRAINTS where r_constraint_name= (select CONSTRAINT_NAME from USER_CONSTRAINTS where table_name=INPUT_PARENT_TABLE and CONSTRAINT_TYPE=P) and CONSTRAINT_TYPE=R;