CODEWITHSUNDEEP
oracle-databaseprimary-key
Buddy
Buddy

Reputation: 13

Query in Oracle to get columns in a table with primary key info

What is a query to get table description with columns with columns part of primary key tagged? For instance, it would return something like this:

Table   Column       is_part_of_pk
-----   ------       -------------
ADDRESS  ID           1
ADDRESS  ADDR_LINE_1  0
ADDRESS  ADDR_CITY    0
STUDENT  FIRST_NAME   1
STUDENT  LAST_NAME    1
STUDENT  CLASS_NAME   0

All the columns in a table are listed and the ones that are or are part of a primary key are 'tagged'. I have tried to select against user_tab_columns, all_cons_columns and all_constraints but i get duplicate columns. Thanks.

Upvotes: 1

Views: 242

Answers (1)

Dmitry.Samborskyi
Dmitry.Samborskyi

Reputation: 485

Try this

select col.table_name , col.column_name, 
case when exists(select 'x' from USER_CONSTRAINTS l 
join USER_CONS_COLUMNS ll on LL.CONSTRAINT_NAME = L.CONSTRAINT_NAME
where l.table_name = col.table_name and l.constraint_type = 'P' and ll.column_name = col.column_name) then 1 else 0 end is_part_of_pk
 from USER_TAB_COLUMNS col where table_name = :some_table_name
 order by column_id;

Upvotes: 1

Related Questions