Get the primary key name of a table
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 a column name, remember that PK can have multiple columns! First, you need to create a utility type because of the aggregate collect () 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 sub-tab, you can write a utility function that will take NT as input and provide a comma-delimited concatenated string as output. Just don't have time to do it yourself;)
source to share
Try to join USER_TABLES with USER_CONSTRAINTS .
In USER_CONSTRAINTS, the filter for all rows with constraint_type is "P" (primary key). There you will get the index_name and index_owner values that you could join with USER_INDEXES and get the column names from your main file.
I wish I knew an easier way to do this ... But I don't.
Good luck!
source to share
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;
source to share