3.Oracle表和字段查询

1.查询表字段、类型、注释

SELECT A.COLUMN_NAME,B.comments,A.DATA_TYPE
FROM   USER_TAB_COLUMNS A
LEFT JOIN user_col_comments B ON A.TABLE_NAME=B.table_name AND A.COLUMN_NAME=B.column_name
WHERE  A.TABLE_NAME = '表名'
ORDER  BY A.COLUMN_ID;

2.获取表字段与数据类型

select * from user_tab_columns 
where Table_Name='表名' 
order by column_name

3.获取表注释

select * from user_tab_comments  where Table_Name='表名'

4.获取表字段注释

select * from user_col_comments 
where Table_Name='表名'
order by column_name；

5.通过user_constraints查看所有约束

select   *   from   user_constraints  where   table_name   =   upper('表名');

6.查看主键约束

select   *   from   user_constraints   where   constraint_type='P' and  TABLE_name=upper('表名');

7.获取表：

select table_name from user_tables; //当前用户的表
select table_name from all_tables; //所有用户的表
select table_name from dba_tables; //包括系统表
select table_name from dba_tables where owner='用户名'