查询表结构的部分语句

查询当前用户下所有表

select * from user_tables;

查询用户有所表下字段信息

select * from user_tab_columns;

查询用户所有字段信息的注释

select * from user_col_comments;

查询用户字段约束和字段对应关系

select * from user_cons_columns ;

查询用户所有约束信息

select * from user_constraints;

查看数据库所有表的外键

内容要根据具体情况修改

Select b.table_name 主键表名,
           b.column_name 主键列名,
           a.table_name 外键表名,
           a.column_name 外键列名
From (Select a.constraint_name,
                       b.table_name,
                       b.column_name,
                       a.r_constraint_name
          From all_constraints a, all_cons_columns b
         Where a.constraint_type = 'R'
           And a.constraint_name = b.constraint_name) a,
       (Select Distinct a.r_constraint_name, b.table_name, b.column_name
          From all_constraints a, all_cons_columns b
         Where a.constraint_type = 'R'
           And a.r_constraint_name = b.constraint_name
                     and a.OWNER = '数据库名' and b.OWNER = '数据库名') b
Where a.r_constraint_name = b.r_constraint_name  ORDER BY b.TABLE_NAME

Oracle查询数据字典语句(需要更改)

select m.*, n.DATA_TYPE,n.DATA_LENGTH from
(select * from all_COL_COMMENTS where OWNER LIKE '数据库名') m
INNER JOIN 
(select a.data_type, a.data_length, a.table_name, a.column_name 
from all_tab_columns a   where OWNER LIKE '数据库名') n
on m.table_name = n.table_name and m.column_name = n.column_name ORDER BY m.table_name

导出某个表数据的SQL语句

(运行时把绑定变量:OBJECT_OWNER和:OBJECT_NAME改成自己需要查找的表):

select c.column_name,  
case when data_type = 'CHAR'     
then      data_type||'('||c.char_length||decode(char_used,'B',' BYTE','C',' CHAR',null)||')'    
     when data_type = 'VARCHAR'  
then      data_type||'('||c.char_length||decode(char_used,'B',' BYTE','C',' CHAR',null)||')'    
    when data_type = 'VARCHAR2' 
then      data_type||'('||c.char_length||decode(char_used,'B',' BYTE','C',' CHAR',null)||')'    
    when data_type = 'NCHAR'    
then      data_type||'('||c.char_length||decode(char_used,'B',' BYTE','C',' CHAR',null)||')'    
    when data_type = 'NUMBER' 
then      
case when c.data_precision is null 
then          'NUMBER'      
 else           data_type||'('||c.data_precision||','||c.data_SCALE||')'      
end    
when data_type = 'NVARCHAR' 
then      data_type||'('||c.char_length||decode(char_used,'B',' BYTE','C',' CHAR',null)||')'    
when data_type = 'NVARCHAR2' 
then     data_type||'('||c.char_length||decode(char_used,'B',' BYTE','C',' CHAR',null)||')'    
else      data_type    
end 
data_type,
      decode(nullable,'Y','Yes','No') nullable,  
    c.DATA_DEFAULT,column_id,   com.comments         
    
        from sys.Dba_tab_Columns c, 
           sys.Dba_col_comments com
      where c.owner      = :OBJECT_OWNER 
      and  c.table_name =  :OBJECT_NAME   
      and c.table_name = com.table_name
      and c.owner = com.owner
      and c.column_name = com.column_name                  
      order by column_id
Last modification:June 30th, 2020 at 11:15 am