获取oracle表结构的字段信息

    技术2022-05-11  14

    select a.column_id as 列号, a.column_name as 列名, a.data_type as 类型,   decode(a.data_type,'NUMBER',a.data_precision,a.data_length) as 长度, a.data_scale as 小数位,  decode(e.uniqueness,'UNIQUE','Y','N') as 是否是唯一的, decode(e.key,'Y','Y','N') 是否是主键,  f.comments as 注释, a.nullable as 是否允许空, a.data_default as 默认值from user_tab_columns a, user_col_comments f, (select b.table_name, b.index_name,b.uniqueness, c.column_name,         decode(d.constraint_name,NULL,'N','Y') key  from user_indexes b, user_ind_columns c,      ( select constraint_name from user_constraints where constraint_type='P' ) d  where b.index_name=c.index_name and b.index_name=d.constraint_name(+) ) ewhere a.table_name='TEMPTABLE' and a.table_name=e.table_name(+) and a.column_name=e.column_name(+)  and a.table_name=f.table_name and a.column_name=f.column_nameorder by a.column_id

     

    select TC.table_name , TC.column_name ,TC.DATA_TYPE,TC.data_length,TC.data_precision,TC.nullable,TC.char_col_decl_length,decode(C.constraint_type,'P','PK','U','Unique','R','FK','C','Check',C.constraint_type)from USER_TAB_COLUMNS TC,USER_CONS_COLUMNS CC ,user_constraints Cwhere TC.table_name = 'EMP'and TC.table_name = CC.table_name(+)and TC.column_name = CC.column_name (+)and CC.constraint_name = C.constraint_name(+)

     

    select    A.column_name 字段名,A.data_type 数据类型,A.data_length 长度,A.data_precision 整数位,    A.Data_Scale 小数位,A.nullable 允许空值,A.Data_default 缺省值,B.comments 备注 from  user_tab_columns A,user_col_comments B where a.COLUMN_NAME=b.column_name and    A.Table_Name = B.Table_Name and A.Table_Name='TEST' 


    最新回复(0)