Home » SQL & PL/SQL » SQL & PL/SQL » Urgent - Query the table relationship (Oracle 11g)
Urgent - Query the table relationship [message #567981] Mon, 08 October 2012 22:13 Go to next message
msgforsunil
Messages: 26
Registered: November 2005
Junior Member
Hello All,

How do I query for the relationship(to include the details like parent table, child table, the columns of both parent and child table which forms a relationship) among various tables in a user schema?

How do I infer if the relationship is based on a composite key?

Thanks
Sunil Kumar
Re: Urgent - Query the table relationship [message #567982 is a reply to message #567981] Mon, 08 October 2012 22:20 Go to previous messageGo to next message
BlackSwan
Messages: 22789
Registered: January 2009
Senior Member
Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/

>How do I query for the relationship(to include the details like parent table, child table, the columns of both parent and child table which forms a relationship)

query ALL_CONSTRAINTS & ALL_CONS_COLUMNS
Re: Urgent - Query the table relationship [message #567983 is a reply to message #567982] Mon, 08 October 2012 22:43 Go to previous messageGo to next message
msgforsunil
Messages: 26
Registered: November 2005
Junior Member
Can you please pass the query?

Thanks in advance!
Sunil Kumar
Re: Urgent - Query the table relationship [message #567985 is a reply to message #567983] Mon, 08 October 2012 22:47 Go to previous messageGo to next message
BlackSwan
Messages: 22789
Registered: January 2009
Senior Member
SQL> select constraint_type, count(*) from user_constraints group by constraint_type order by 1;

C   COUNT(*)
- ----------
C       2703
F          3
O         37
P        340
R         29
U         52


consider to Read The Fine Manual to learn what each CONSTRAINT_TYPE represents
Re: Urgent - Query the table relationship [message #567986 is a reply to message #567985] Mon, 08 October 2012 23:05 Go to previous messageGo to next message
msgforsunil
Messages: 26
Registered: November 2005
Junior Member
However, I am looking for information relating to the relationship(like parent table name, child table name, the columns of both parent and child table which forms a relationship). How do I fetch those information?

Thank You.

Re: Urgent - Query the table relationship [message #567987 is a reply to message #567986] Mon, 08 October 2012 23:10 Go to previous messageGo to next message
BlackSwan
Messages: 22789
Registered: January 2009
Senior Member
> How do I fetch those information?
issue SELECT statement.

do you know how to compose valid SQL statements?
How will you determine if any posted SQL provides the correct solution?
Re: Urgent - Query the table relationship [message #567999 is a reply to message #567986] Tue, 09 October 2012 01:21 Go to previous message
Michel Cadot
Messages: 59126
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Use user_constraints and user_cons_columns.
All views are described in Database Reference.

Regards
Michel
Previous Topic: GATHER STATS
Next Topic: query is taking hours to run from toad
Goto Forum:
  


Current Time: Thu Sep 18 09:34:34 CDT 2014

Total time taken to generate the page: 0.10475 seconds