• Oracle查询一个用户的所有表的结构信息的SQL语句


    选择出的信息结果:表名 说明

    select user_tab_comments.table_name as 表名,user_tab_comments.comments as 说明 from user_tab_comments

    选择出的信息结果:表名(备注)、列名、列标注、主键、外键、字段类型、长度、小数位数、是否允许空、默认值

    select
               case
                    when user_tab_comments.comments is null then a.table_name
                      else a.table_name||'('||user_tab_comments.comments||')'
               end as 表名(备注),
               a.column_name as 列名,
               f.comments as 列标注,
               decode(e.key1, 'P', '', '') as 主键,
               decode(e.key2, 'R', '', '') as 外键,
               a.data_type as 字段类型,
               NVL(decode(a.data_type, 'NUMBER', a.data_precision, a.data_length),0) as 长度,
               NVL(a.data_scale,0) as 小数位数,
               a.nullable as 是否为空,
               a.data_default as 默认值
          from
               user_tab_columns a,
                user_col_comments f,
               user_tab_comments,
               ( select
                       uc.table_name,
                        uc.constraint_name,
                       ucc.column_name,
                       uc.constraint_type key1,
                       uc.constraint_type key2
                 from
                          user_constraints uc,
                          user_cons_columns ucc
                    where
                          (uc.constraint_type='R'or uc.constraint_type='P')
                   and uc.constraint_name=ucc.constraint_name
                      and uc.owner=ucc.owner
                      and uc.table_name=ucc.table_name
                      and ucc.table_name not like 'BIN$%==$0'
               ) e
          where
                  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_name
                  and a.table_name = user_tab_comments.table_name
                  and a.table_name not like 'BIN$%==$0'
          order by a.table_name
  • 相关阅读:
    Android 报错Android
    转:JavaWeb学习总结(一) 写得相当不错
    infer 编译代码审查命令记录
    转:infoQ 2015开发者资料下载
    转:java 进阶之路
    转:使用gradle 构建编译程序
    web开发者的博客
    转:http2基本中文翻译
    转:http2的资料与使用
    转:百度手机地图网络性能优化实践
  • 原文地址:https://www.cnblogs.com/tv151579/p/3100872.html
Copyright © 2020-2023  润新知