查看Oracle表结构

如何获得表的详细定义,包括字段名称、字段大小、字段精度、字段默认值、字段描述、是否为空、是否主键,用以下SQL就可以实现,把tablename替换成要查看的表:

select M.column_name FieldName,M.data_type FieldType,M.data_length FieldSize,M.data_precision
Length,M.Data_Scale Scale,M.nullable IsNul,M.Data_default DefaultValue,A.comments Note,(case when B.column_name is null then ‘N’ else ‘Y’ end) MainKey from
(select * from user_tab_columns where upper(Table_name) = upper(‘tablename’)) M left join user_col_comments A ON M.COLUMN_NAME=A.COLUMN_NAME and M.Table_Name = A.Table_Name
left join (select a.table_name,b.column_name from user_constraints a, user_cons_columns b where a.constraint_name=b.constraint_name and upper(a.table_name)=upper(‘tablename’) and a.constraint_type=’P') B ON M.Table_Name = B.TABLE_NAME and M.COLUMN_NAME=B.COLUMN_NAME order by M.column_id

列查询

select * from cols WHERE TABLE_name=upper(‘function_info’)

select * from user_tab_columns WHERE TABLE_name=upper(‘function_info’)

约束查询

select * from user_constraints WHERE TABLE_name=upper(‘function_info’)

发表评论

电子邮件地址不会被公开。 必填项已用 * 标注

您可以使用这些 HTML 标签和属性: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>