Oracle查询数据库中的所有主键和索引

查询数据库中的所有主键和索引

//查出所有索引名、表明、字段名
select t.*,i.index_type from user_ind_columns t,user_indexes i where t.index_name = i.index_name and t.table_name = i.table_name
//查看所有主键
select cu.* from user_cons_columns cu, user_constraints au where cu.constraint_name = au.constraint_name and au.constraint_type = 'P'
//分组查主键
 select cu.TABLE_NAME ,cu.CONSTRAINT_NAME ,wm_concat(cu.COLUMN_NAME) from user_cons_columns cu, user_constraints au where cu.constraint_name = au.constraint_name and au.constraint_type = 'P'
GROUP BY  cu.TABLE_NAME,cu.CONSTRAINT_NAME
//分组查主键
select cu.TABLE_NAME ,cu.CONSTRAINT_NAME ,LISTAGG(cu.COLUMN_NAME, ',')WITHIN GROUP(ORDER BY cu.TABLE_NAME)  from user_cons_columns cu, user_constraints au where cu.constraint_name = au.constraint_name and au.constraint_type = 'P'
GROUP BY  cu.TABLE_NAME,cu.CONSTRAINT_NAME
//分组查索引
SELECT t.TABLE_NAME,t.INDEX_NAME,LISTAGG(t.COLUMN_NAME, ',')WITHIN GROUP(ORDER BY t.TABLE_NAME) from user_ind_columns t,user_indexes i where t.index_name = i.index_name and t.table_name = i.table_name
GROUP BY  t.TABLE_NAME,t.INDEX_NAME