Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1655
  • 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

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

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