查询Oracle表结构信息（走数模用）

select '新增字段' as "变更类型",
       '系统' as "信息系统名称",
       'DEKBGMS' as "数据库名称",
       c.TABLE_NAME as "物理实体名称",
       (select men.COMMENTS
          from user_tab_comments men
         where men.Table_Name = c.Table_Name) as "物理实体描述",
       c.COLUMN_NAME "属性名称",
       (select cmen.COMMENTS
          from user_col_comments cmen
         where cmen.Table_Name = c.Table_Name
           and cmen.COLUMN_NAME = c.COLUMN_NAME) as "属性描述",
       '否' as "是否主健",
       '否' as "是否外键",
       case
         when (c.DATA_TYPE = 'DATE' or c.DATA_TYPE = 'CLOB' or c.DATA_TYPE='BLOB') then
          c.DATA_TYPE
         else
          c.DATA_TYPE || '(' ||
          decode(c.CHAR_LENGTH, 0, c.DATA_PRECISION, c.CHAR_LENGTH) || ')'
       end "数据类型",
       c.CHAR_LENGTH as "长度",
       decode(c.NULLABLE, 'N', '是', '否') as "是否必填",
       '' as "默认值",
       '' as "取值范围",
       '' as "备注"
  from user_tab_columns c
 where Table_Name in ('S_KEW_PILE_SORTS',
                      'S_KEW_ITEMP_TOPIC')
 order by c.TABLE_NAME, c.column_name;

 

本文地址：https://blog.csdn.net/LinBilin_/article/details/107635410