Link to home
Start Free TrialLog in
Avatar of k_murli_krishna
k_murli_krishnaFlag for India

asked on

Table and PK columns

1) We have Oracle 10g r2 on windows 2000 server.

2) How to find primary keys fields of a set of tables in a database using a Query?
 
 Query output should be
 
 Tablename, primarykey field name
ASKER CERTIFIED SOLUTION
Avatar of fmonroy
fmonroy
Flag of Mexico image
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of peterside7
peterside7
And the same query using dba_   tables (if you have the dba privilege), not getting the SYS or SYSTEM constraints

select 'table= '||ucc.table_name||'      col'||ucc.position||' = '||ucc.column_name
from dba_constraints uc
 inner join dba_cons_columns ucc on
 uc.constraint_name = ucc.constraint_name
where uc.constraint_type = 'P'
and uc.owner not in ('SYS','SYSTEM')
order by ucc.table_name,ucc.position
Avatar of fmonroy
fmonroy
Flag of Mexico image
i agree with peterside7, only a comment:

you need to include the owner in the resultset if retrieving from dba_tables because you will get scrambled data if several users use the same table names in their schema.

what do you think?
Avatar of peterside7
peterside7
Yes, exactly, you need the owner :

select ucc.owner, 'table= '||ucc.table_name||'      col'||ucc.position||' = '||ucc.column_name
from dba_constraints uc
 inner join dba_cons_columns ucc on
 uc.constraint_name = ucc.constraint_name
where uc.constraint_type = 'P'
and uc.owner not in ('SYS','SYSTEM')
order by ucc.table_name,ucc.position