我如何获得数据库名称，大小，表名称，对象，索引，检查约束，外键在一个单一的查询

Question

我正在寻找一个查询，以获得主题中提到的所有细节。

Answer 1

select user schema,'1.FIELDS' obj, null name, c.TABLE_NAME,c.COLUMN_ID col_order,c.COLUMN_NAME,
c.DATA_TYPE,c.DATA_LENGTH,c.DATA_PRECISION,c.DATA_SCALE,c.NULLABLE,null condition
from user_tab_columns c
union all
select user,'2.INDEX '||uniqueness,index_name,table_name,i.column_position, i.column_name,null,
null,null,null,null,null
from user_ind_columns i inner join user_indexes using(table_name,index_name)
union all
select user,'3.CONSTR', constraint_name, table_name, csc.position, csc.column_name,null,null,
null,null,null,cs.search_condition
from user_constraints cs inner join user_cons_columns csc using (table_name,constraint_name)
where constraint_type='C'
union all
select user,'4.FK', constraint_name, table_name, csc.position, csc.column_name||'->'||r_constraint_name,
null,null,null,null,null,cs.search_condition
from user_constraints cs inner join user_cons_columns csc using (table_name,constraint_name)
where constraint_type='R'
order by  4, 2, 3, 5

这将显示有关当前模式中所有表的信息。对于每个表，它显示1)字段2)索引3)约束4)外键