查询Oracle数据库 表结构

--简单查询

select *
  from ALL_TAB_COLS A
 where lower(A.Table_Name) = '要查询的表名';

 

--综合查询

select C.OWNER as 用户名,
       A.TABLE_NAME as 表名,
       E.COMMENTS as 表中文名,
       A.COLUMN_NAME as 列名,
       decode(A.DATA_TYPE,
              'CHAR',
              A.DATA_TYPE || '(' || A.DATA_LENGTH || ')',
              'DATE',
              A.DATA_TYPE,
              'NUMBER',
              A.DATA_TYPE || '(' || A.DATA_PRECISION || ',' || A.DATA_SCALE || ')',
              'VARCHAR2',
              A.DATA_TYPE || '(' || A.DATA_LENGTH || ')') as 数据类型,
       decode(C.COLUMN_NAME, null, '', '是') as 主键,
       decode(D.INDEX_NAME, null, '', '是') as 索引,
       decode(A.NULLABLE, 'N', '', 'Y', '可空') as 可为空,
       B.comments as 备注
  from sys.user_tab_cols A,
       sys.user_col_comments B,
       (select col.column_name, c.table_name, col.owner
          from user_constraints c, user_cons_columns col
         where c.constraint_name = col.constraint_name
           and c.constraint_type = 'P') C,
       (select ind.index_name, c.table_name
          from user_constraints c, user_indexes ind
         where c.table_name = ind.table_name
           and c.constraint_type = 'P') D,
       sys.user_tab_comments E
 where lower(A.TABLE_NAME) = '要查询的表名'
   and A.TABLE_NAME = B.table_name
   and A.COLUMN_NAME = B.column_name
   and A.Table_Name = E.TABLE_NAME
   and A.Table_Name = C.TABLE_NAME(+)
   and A.COLUMN_NAME = C.COLUMN_NAME(+)
   and A.Table_Name = D.TABLE_NAME(+)
   and A.COLUMN_NAME = D.INDEX_NAME(+)
 order by A.TABLE_NAME, A.Column_Id;