Oracle获取所有表名信息和获取指定表名字段信息

 

 

获取所有表名信息

select t.owner table_dbname,t.table_name, t.num_rows table_rows, s.bytes table_size from all_tables t left join dba_segments s on s.segment_name = t.table_name and s.owner = t.owner and s.segment_type='TABLE' where 1 = 1
AND  t.owner='库名' 

 

 

获取指定表名的字段信息

select a.column_name, a.data_type, (case a.nullable when 'Y' then 1 else 0 end) as is_nullable, a.data_length, a.data_precision, (case b.constraint_type when 'P' then 1 else 0 end) as column_key, c.comments as column_comment 
from all_tab_columns a left join (select a.column_name,b.constraint_type from all_cons_columns 
a inner join user_constraints b on b.owner = a.owner and b.constraint_name = a.constraint_name and b.table_name = a.table_name 
where a.table_name = '表名' and a.owner = '库名' and b.constraint_type = 'P')b on b.column_name = a.column_name left join all_col_comments 
c on c.owner = a.owner and c.table_name = a.table_name and c.column_name = a.column_name where a.table_name = '表名' and a.owner = '库名' and 
a.data_type != 'CLOB'