Oracle 查看表常用相关SQL

Oracle 查看表常用相关SQL

1、Oracle查看所有表和字段

select * from all_tables where owner='VTATEST';

all_tables查出来是查得所有用户下的表，当然也包括你登录的用下的表，然后加一个where你要查的那个用户名就可以了。（记得用户名要大写）

select * from user_tables;

查的单纯是你所登录的用户下的表，不会显示其他用户下的表。

select * from tabs;

这个和第2个类一样，但是书写更简单，也是查你所在用户下的表，本人用的最多的是这个。

获取表字段：

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

获取表注释：

select * from user_tab_comments where Table_Name='用户表'order by Table_Name

获取字段注释：

select * from user_col_comments where Table_Name='用户表'order by column_name

/* 获取表：*/

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='zfxfzb'

/*
user_tables：
table_name,tablespace_name,last_analyzed等
dba_tables：
ower,table_name,tablespace_name,last_analyzed等
all_tables：
ower,table_name,tablespace_name,last_analyzed等
all_objects：
ower,object_name,subobject_name,object_id,created,last_ddl_time,timestamp,status等
/
/ 获取表字段：*/

select * from user_tab_columns where Table_Name='用户表';
select * from all_tab_columns where Table_Name='用户表';
select * from dba_tab_columns where Table_Name='用户表';

/* user_tab_columns：
table_name,column_name,data_type,data_length,data_precision,data_scale,nullable,column_id等
all_tab_columns ：
ower,table_name,column_name,data_type,data_length,data_precision,data_scale,nullable,column_id等
dba_tab_columns：
ower,table_name,column_name,data_type,data_length,data_precision,data_scale,nullable,column_id等
/
/ 获取表注释：*/

select * from user_tab_comments

/*
user_tab_comments：table_name,table_type,comments
相应的还有dba_tab_comments，all_tab_comments，这两个比user_tab_comments多了ower列。
/
/ 获取字段注释：/
select * from user_col_comments
/
user_col_comments：table_name,column_name,comments
相应的还有dba_col_comments，all_col_comments，这两个比user_col_comments多了ower列。
*/

2、Oracle查询所有表

select a.TABLE_NAME, b.COMMENTS
  from user_tables a, user_tab_comments b
 WHERE a.TABLE_NAME = b.TABLE_NAME
 order by TABLE_NAME;

3、Oracle查询没有主键的表

 select table_name
   from user_tables a
  where not exists (select *
           from user_constraints b
          where b.constraint_type = 'P'
            and a.table_name = b.table_name);

查主键名称：

select * from user_constraints 
where table_name = 'AAA' 
and constraint_type ='P'; 

查主键对应的列：

select * from user_cons_columns 
where table_name = 'AAA' 
and constraint_name = 'PK_AAA'; (PK_AAA 是主键名)

查索引名称：

select * from user_indexes where table_name = 'AAA'; 

查索引对应的列：

select * from user_ind_columns 
where table_name = 'AAA' 
and index_name = 'INDX_BA'; (INDX_BA 是 索引名)

了解几个字典表的用处 如：user_constraints
添加主键：

alter table TableName
add constraint PK NAME
primary key("ACCOUNTINGTASKID"...);

删除主键：

alter table EXTRACTED_POSTING(tablle name)
drop constraint EXTRACTED_POSTING_ACCTID_PK;(PK name)

4、Oracle中如何查询一个表的所有字段名和数据类型
查询语法

select A.COLUMN_NAME,A.DATA_TYPE  from user_tab_columns A
where TABLE_NAME='表名'

查询例子

select A.COLUMN_NAME,A.DATA_TYPE  from user_tab_columns A
where TABLE_NAME='PUB_GOODS'

添加排序后例子

select A.COLUMN_NAME,A.DATA_TYPE  from user_tab_columns A
where TABLE_NAME='PUB_GOODS'
order by A.COLUMN_ID asc

5、Oracle 查询所有表最后一个字段不等于 “需要过滤字段名称”

DECLARE
  --a表游标定义
  cursor a_cursor is select TABLE_NAME from user_tables;
  --b表游标定义
  cursor b_cursor(str1 string) is (select * from (select COLUMN_NAME FROM user_tab_columns where TABLE_NAME = str1 order by COLUMN_ID desc) where rownum=1);
BEGIN
for a_cur in a_cursor loop
 --DBMS_OUTPUT.PUT_LINE('1--->' || a_cur.TABLE_NAME);  
    for b_cur in b_cursor(a_cur.TABLE_NAME) loop     
      if(b_cur.COLUMN_NAME <> 'ATTRIBUTE5') then
          DBMS_OUTPUT.PUT_LINE(a_cur.TABLE_NAME);
      end if;
  end loop;  
end loop;
END;

6、Oracle 当前用户可访问的表、视图和群集的列的相关信息
表：ALL_TAB_COLS
字段:
　　OWNER：表，视图及群集的Owner
　　TABLE_NAME： 表，视图及聚簇的名称
　　COLUMN_NAME： 字段名
　　DATA_TYPE ：字段的数据类型
　　DATA_TYPE_OWNER： 字段类型的owner
　　DATA_LENGTH ：字段长度

SELECT * FROM All_Tab_Cols t WHERE t.TABLE_NAME='DEL_REGION_SHIP_NOTICE';
SELECT * FROM Dba_Tables t WHERE t.TABLE_NAME='DEL_REGION_SHIP_NOTICE';
SELECT * FROM user_Tab_Cols t WHERE t.TABLE_NAME='DEL_REGION_SHIP_NOTICE';