Oracle查询表结构

 

1. select * from all_tab_cols A where lower(A.Table_Name) = '表名称';

2. select  b.TABLE_NAME  表名,
c.comments  表评论,
b.COLUMN_ID  字段信号,
b.COLUMN_NAME  字段名,
b.DATA_LENGTH  数据长度,
b.DATA_PRECISION  整数位,
b.DATA_SCALE  小数位,
a.comments  列描述
from all_col_comments a,all_tab_columns b,all_tab_comments c
where a.table_name=b.TABLE_NAME and a.table_name=c.table_name
and a.column_name=b.COLUMN_NAME
and a.owner=b.OWNER and a.owner=c.owner
and lower(a.table_name)='表名称'

3. select C.OWNER 用户,A.TABLE_NAME 表名,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, 'No', 'Yes') as 主键,
    decode(A.NULLABLE, 'N', 'NOT NULL', 'Y', 'NULL') 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
  　　where lower(A.TABLE_NAME) = 'invd020_invslip'
  　　and A.TABLE_NAME = B.table_name
  　　and A.COLUMN_NAME = B.column_name
  　　and A.Table_Name = C.TABLE_NAME(+)
  　　and A.COLUMN_NAME = C.COLUMN_NAME(+)
  　　order by A.TABLE_NAME, A.Column_Id;