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%'
RE: How to find the Foreign keys of all child tables when pass the Parent Table name.
MY DEAR
PLZ QUERY THE DATA DICTIONARY VIEW CALLED 'USER_DEPENDENCIES' AND PASS YOUR PARENT TABLENAME IN THE NAME COLUMN AND ALSO GIVE THE TYPETABLE IN THE WHERE CLAUSE OF QUERY.
RE: How to find the Foreign keys of all child tables when pass the Parent Table name.
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';
RE: How to find the Foreign keys of all child tables when pass the Parent Table name.
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
