Oracle根据表名查询表的主键和唯一索引

查询表的主键列名称

SELECT
 tt.table_name,
 listagg (tt.column_name, ',') WITHIN GROUP (ORDER BY tt.table_name) as pk
FROM
 (
  SELECT
   uc.table_name,
   ucc.column_name
  FROM
   user_constraints uc,
   user_cons_columns ucc,
   user_indexes ui
  WHERE 
   uc.table_name = ucc.table_name
  AND uc.table_name = ui.table_name
  AND uc.table_name = 'OR36_OBJECT_DEL_ANNOT_ASK_B'
  AND uc.constraint_type = 'P'
  AND uc.constraint_name = ucc.constraint_name
  GROUP BY
   uc.table_name,
   ucc.column_name
 ) tt
GROUP BY
 tt.table_name ;

获取唯一索引（ listagg 是组装guoup by 后的字符串）

SELECT
 t.index_name, listagg(t.column_name, ',') within group(order by t.index_name) as uniqueIndex
FROM
 user_ind_columns t,
 user_indexes i
WHERE
 t.index_name = i.index_name
AND i.uniqueness = 'UNIQUE'
AND t.table_name = 'OR14_ENACCTINF_D'
group by t.index_name ;