Home » SQL & PL/SQL » SQL & PL/SQL » Help with keys
Help with keys [message #245459] Sun, 17 June 2007 11:13 Go to next message
saikrishna19
Messages: 2
Registered: June 2007
Junior Member
Can some dba post a query which can pull all the PK and FK columns from the entire schema in the below format. Thanks in advance.

FK_Table FK_columns PK_Table PK_columns
======== ========== ========= ==========
Re: Help with keys [message #245460 is a reply to message #245459] Sun, 17 June 2007 11:15 Go to previous messageGo to next message
saikrishna19
Messages: 2
Registered: June 2007
Junior Member
Can some dba post a query which can pull all the PK and FK columns from the entire schema in the below format. Thanks in advance.

FK_Table **** FK_columns **** PK_Table **** PK_columns
Re: Help with keys [message #245465 is a reply to message #245460] Sun, 17 June 2007 12:49 Go to previous messageGo to next message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Can you:
1/ Post your Oracle version
2/ Tell us where you searched to not find a solution
3/ Post what you already tried by yourself

Clue: use dba_constraints and dba_cons_columns.

Regards
Michel
Re: Help with keys [message #245543 is a reply to message #245465] Mon, 18 June 2007 01:44 Go to previous message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
It's all in the manuals, but hey: fresh week, fresh start.

- user_constraints lists all the constraints defined on the current schema.
- user_cons_columns lists the columns of the constraints in the current schema.

For more details, visit the Oracle documentation website (and find out about related views like ALL_CONSTRAINTS)

When you know that, you can easily create a query like this:
SELECT uc.constraint_name fk_name
     , cc1.table_name     fk_table
     , cc1.column_name    fk_column
     , cc2.table_name     pk_table
     , cc2.column_name    pk_column
FROM   user_constraints uc
   ,   user_cons_columns cc1
   ,   user_cons_columns cc2
WHERE  uc.constraint_type   = 'R'
AND    uc.constraint_name   = cc1.constraint_name
AND    uc.r_constraint_name = cc2.constraint_name


MHE
Previous Topic: SQL query to reverse
Next Topic: plz solve this query....
Goto Forum:
  


Current Time: Tue Dec 06 14:26:05 CST 2016

Total time taken to generate the page: 0.06722 seconds