CODEWITHSUNDEEP
sqloracle-database
Sheldon
Sheldon

Reputation: 2628

Get table's primary key's name

I was wondering how can I list the user tables name with its primary key name as the second column?

I'm doing half the job but I doesn't know what more to do.

select table_name from user_tables ORDER BY (table_name) ASC

Does anyone knows how?

Thank you

Upvotes: 2

Views: 1197

Answers (3)

user890191
user890191

Reputation: 11

you can use the following query 

SELECT cols.table_name, cols.column_name, cols.position, cons.status, cons.owner
FROM all_constraints cons, all_cons_columns cols
WHERE cons.constraint_type = 'P'
AND cons.constraint_name = cols.constraint_name
AND cons.owner = cols.owner
ORDER BY cols.table_name, cols.position;

Upvotes: 1

Andrew G
Andrew G

Reputation: 2496

This simple query gives you a list of tables along with the PK constraint name (but not the column name):

select     ut.table_name, uc.constraint_name
from       user_tables ut
left join  user_constraints uc
  on       ut.table_name = uc.table_name
  and      uc.constraint_type = 'P';

If you want the column name, remember that a PK can have multiple columns! We need to create a utility type first because of the collect() aggregate function:

create type ty_varchar_nt as table of varchar2(4000);

select     ut.table_name, uc.constraint_name, cast(collect (uic.column_name) as ty_varchar_nt) as pk_cols
from       user_tables ut
left join  user_constraints uc
  on       ut.table_name = uc.table_name
  and      uc.constraint_type = 'P'
left join  user_ind_columns uic
  on       uc.index_name = uic.index_name
group by ut.table_name, uc.constraint_name;

If you don't like the formatting of the nested tab, you can write a utility function which will take an NT as input and provide concatenated comma-delimited string as output. Just don't have the time to do it myself ;)

Upvotes: 3

Pablo Santa Cruz
Pablo Santa Cruz

Reputation: 181280

Try joining USER_TABLES with USER_CONSTRAINTS.

In USER_CONSTRAINTS filter all rows with constraint_type equals to 'P' (primary key). There, you'll get index_name and index_owner values that you could join with USER_INDEXES and get column names from your PRIMARY KEY.

I wish I knew a more straightforward way of doing this... But I don't.

Good luck!

Upvotes: 1

Related Questions