How to get all the tables refered by a particular Table

[lalleima]

Hi

How can we get all the tables refered by a particular Table Thourgh a SQL query? For eg T1 is the Parent table of T2, How can we get T1 corresponding to T2 through a query.

 

[Turkbear]

Hi,
Could you give an example of a query that explains your question?

In general you can join tables by a common key field like
select * from T1, T2 where T1.field = T2.field;

 

[R1972]

Hi,

Execute the following query to find out the reference tables of your given parent table.


SELECT * FROM ALL_CONSTRAINTS
WHERE R_CONSTRAINT_NAME IN(
SELECT CONSTRAINT_NAME FROM ALL_CONSTRAINTS
WHERE TABLE_NAME='<parent_table>')


Regards,
Raj

 

[lalleima]

Thank U all for all your consideration but I think I have found it. Its somthing like this:

select table_name from all_constraints where CONSTRAINT_NAME in (
select R_CONSTRAINT_NAME from all_constraints where CONSTRAINT_TYPE='R'
and upper(table_name) like upper('T2'))

Suppose if T1 is the parent table of T2 then This Query will give us T1 as a result. But still this Query is taking some time. Any suggestion to optimise this query and to make it more efficient is wellcomeed.

Thank U Raj but with your query we can only identify the table manually, Actually I have to embede this query in our application and needed the exact Table Name.

 

[lalleima]

I think Raj didn't quit get the Question. It is actually opposite what U R doing I wanted the parent Table name by giving the Child Table.

 

[Turkbear]

Your query uses functions and that prevents indexes from being used..Since all table names are upper_case in Oracle ( unless you created them badly)
you should be able to eliminate the

and upper(table_name) like upper('T2'))

and just use

and table_name = 'T2')

 

[R1972]

Hi lalleima,

Yes, you are right. I defined that query in reverse order. I hope it would helpful as a hint.

Regards,
Raj