查询表结构信息sql

select
    'DQMS' 库名,A.Table_Name 表名,C.comments 表中文名,A.column_name 字段名,B.comments 备注,A.data_type 数据类型,A.data_length 长度,A.data_precision 整数位,
        case
          when (select count(*) from user_cons_columns c 
            where c.table_name=A.TABLE_NAME and c.column_name=A.COLUMN_NAME and c.constraint_name=
                (select d.constraint_name from user_constraints d where d.table_name=c.table_name and d.constraint_type   ='P')
                 )>0 then '1' else '0'end as 主键,
                 null 外键,
                 A.nullable 允许空值,A.Data_default 缺省值
from
    user_tab_columns A,user_col_comments B,user_tab_comments C
where
    A.Table_Name = B.Table_Name
    and A.Column_Name = B.Column_Name
    and A.Table_Name = C.Table_Name
    --and A.Table_Name = 'ACT_RU_TASK'
    order by A.Table_Name
    ;

外键：

select distinct(ucc.column_name) column_name,ucc.table_name
--,rela.table_name,rela.column_name column_name1
from
 user_constraints uc,user_cons_columns ucc,
 (select t2.table_name,t2.column_name,t1.r_constraint_name from user_constraints t1,user_cons_columns t2 where t1.r_constraint_name=t2.constraint_name 
 --and t1.table_name='ONLINEXLS'
 ) rela
where
 uc.constraint_name=ucc.constraint_name
 and uc.r_constraint_name=rela.r_constraint_name
 --and uc.table_name='PRODUCT'
 order by ucc.table_name,ucc.column_name
 ;