数据库表结构及索引查询

基础用户视图

select * from user_tables;--用户表
select * from user_tab_comments;--用户表注释

select * from cols; --表的列
select * from user_col_comments; --用户列注释

select * from user_ind_columns;--用户表索引列
select * from user_indexes;--用户索引

select * from user_cons_columns;--用户约束列
select * from user_constraints;--用户约束

查询语句

--索引查询
select  t.TABLE_NAME,t.INDEX_NAME,t.COLUMN_NAME,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 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,user_tables ut
 where t.index_name = i.index_name
 and t.table_name = i.table_name
   and ut.table_name = i.table_name
 GROUP BY t.TABLE_NAME, t.INDEX_NAME

 --查询表字段及说明
select t1.COLUMN_NAME 字段名,
       t2.DATA_TYPE   类型,
       t2.DATA_LENGTH 长度,
       t2.NULLABLE    能否为空,
       t1.COMMENTS    说明
  from user_col_comments t1, cols t2
 where t1.COLUMN_NAME = t2.COLUMN_NAME
   and t1.TABLE_NAME = t2.TABLE_NAME
   and t1.TABLE_NAME = 'PAY_BANKACCBOOK'
 order by t1.COLUMN_NAME;

--查询表字段及说明
select t1.COLUMN_NAME 字段名,
       t2.DATA_TYPE   类型,
       t2.DATA_LENGTH 长度,
       t1.COMMENTS    说明,
       t3.INDEX_NAME 索引名
  from user_col_comments t1, cols t2,user_ind_columns t3
 where t1.COLUMN_NAME = t2.COLUMN_NAME
 and t1.COLUMN_NAME = t3.COLUMN_NAME
   and t1.TABLE_NAME = t2.TABLE_NAME
   and t1.TABLE_NAME = t3.TABLE_NAME
   and t1.TABLE_NAME = 'PAY_BANKACCBOOK'
 order by t1.COLUMN_NAME;