• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1671
  • Last Modified:

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
0
k_murli_krishna
Asked:
k_murli_krishna
  • 2
  • 2
1 Solution
 
fmonroyCommented:
here you have the constraints:

SQL> desc user_constraints
 Name                                      Null?    Type
 ----------------------------------------- -------- ------------
 OWNER                                     NOT NULL VARCHAR2(30)
 CONSTRAINT_NAME                           NOT NULL VARCHAR2(30)
 CONSTRAINT_TYPE                                    VARCHAR2(1)
 TABLE_NAME                                NOT NULL VARCHAR2(30)
 SEARCH_CONDITION                                   LONG
 R_OWNER                                            VARCHAR2(30)
 R_CONSTRAINT_NAME                                  VARCHAR2(30)
 DELETE_RULE                                        VARCHAR2(9)
 STATUS                                             VARCHAR2(8)
 DEFERRABLE                                         VARCHAR2(14)
 DEFERRED                                           VARCHAR2(9)
 VALIDATED                                          VARCHAR2(13)
 GENERATED                                          VARCHAR2(14)
 BAD                                                VARCHAR2(3)
 RELY                                               VARCHAR2(4)
 LAST_CHANGE                                        DATE
 INDEX_OWNER                                        VARCHAR2(30)
 INDEX_NAME                                         VARCHAR2(30)
 INVALID                                            VARCHAR2(7)
 VIEW_RELATED                                       VARCHAR2(14)

users_constraints.constraint_type='P' are the primary key ones.

here you have the contraints' columns:

SQL> desc user_cons_columns
 Name                                      Null?    Type
 ----------------------------------------- -------- --------------
 OWNER                                     NOT NULL VARCHAR2(30)
 CONSTRAINT_NAME                           NOT NULL VARCHAR2(30)
 TABLE_NAME                                NOT NULL VARCHAR2(30)
 COLUMN_NAME                                        VARCHAR2(4000)
 POSITION                                           NUMBER

so, if you join those views using the constraint name you can get the primary keys fields:

  select ucc.table_name, ucc.column_name
    from user_constraints uc
         inner join user_cons_columns ucc on
         uc.constraint_name = ucc.constraint_name
   where uc.constraint_type = 'P'
order by ucc.table_name,
         ucc.position
0
 
peterside7Commented:
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
0
 
fmonroyCommented:
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?
0
 
peterside7Commented:
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
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now