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;