ORACLE pt.3 【エクセルで利用するテーブル定義書を抽出】

EXCELで利用する前提で作ってみました。
権限がないと取得できないかも知れません。
後ろのほうにある upper(o.owner) = 'SCHEMA' に指定する値は環境に合わせて修正してください。
結果をエクセルのシートに貼り付けて利用します。
テーブル書式にしたり、条件付き書式で色分けしたり工夫してください。
フィルタを使うと同じカラム名で型やサイズの違いなど見つけ易いです。
また、新しいシートに張り付けても、EXCELは値のみを張り付けられますので
常に最新のテーブル定義書を利用しましょう。

-- エクセルで利用するテーブル定義書
select o.owner                      as "SCHEMA"
      ,n.comments                   as tableName
      ,c.table_name                 as tableID
      ,c.column_id                  as colNo
      ,d.comments                   as colName
      ,c.column_name                as colID
      ,case upper(c.data_type)
        when 'TIMESTAMP(6)' then
          'TIMESTAMP'
        when 'NVARCHAR2'     then
          c.data_type || '(' || to_char(c.char_length) || ')'
        when 'VARCHAR2'     then
          c.data_type || '(' || to_char(c.char_length) || ')'
        when 'NUMBER'       then
          case
            when ((c.data_precision is null) and (c.data_scale is null)) then
              c.data_type
            else
              c.data_type || '(' || to_char(c.data_precision) || ',' || to_char(c.data_scale) || ')'
          end
        when 'DATE'         then
          c.data_type
        when 'CHAR'         then
          c.data_type || '(' || to_char(c.char_length) || ')'
        when 'LONG'         then
          c.data_type || '(' || to_char(c.data_precision) || ')'
        when 'BLOB'         then
          c.data_type || '(' || to_char(c.data_length) || ')'
        when 'CLOB'         then
          c.data_type || '(' || to_char(c.data_length) || ')'
        when 'NCLOB'       then
          c.data_type || '(' || to_char(c.data_length/2) || ')'
        else
          c.data_type
       end                          as DataType
      ,p.constraint_type            as colPK
      ,q.column_position            as "UNIQUE"
      ,case when pt.column_name is not null then 'PT'
        else ' '
       end                          as PartKey
      ,case c.nullable
        when 'Y' then ''
        else c.nullable
       end                          as nil
      ,c.data_type                  as colType
      ,case upper(c.data_type)
        when 'NVARCHAR2'     then
          to_char(c.char_length)
        when 'VARCHAR2'     then
          to_char(c.char_length)
        when 'NUMBER'       then
          to_char(c.data_precision)
        when 'CHAR'         then
          to_char(c.char_length)
        when 'LONG'         then
          to_char(c.data_precision)
        when 'BLOB'         then
          to_char(c.data_precision)
        when 'CLOB'         then
          to_char(c.data_precision)
        when 'NCLOB'       then
          to_char(c.data_length/2)
        else
          ''
       end                          as colSize
      ,case upper(c.data_type)
        when 'NUMBER'       then
          to_char(c.data_scale)
        else
          ''
       end                          as colScale
  from sys.all_objects                  o
 inner join sys.all_tables              t
    on upper(t.owner)       = upper(o.owner)
   and upper(t.table_name)  = upper(o.object_name)
   and upper(o.object_type) = 'TABLE'
 inner join sys.all_tab_columns         c
    on upper(c.owner)       = upper(t.owner)
   and upper(c.table_name)  = upper(t.table_name)
  left outer join sys.all_tab_comments  n
    on n.owner      = t.owner
   and n.table_name = t.table_name
   and upper(n.table_type) = 'TABLE'
  left outer join sys.all_col_comments  d
    on upper(d.owner)       = upper(c.owner)
   and upper(d.table_name)  = upper(c.table_name)
   and upper(d.column_name) = upper(c.column_name)
  left outer join (select c.table_name
                         ,c.column_name
                         ,case
                            when count(1)=1 then to_char(min(c.position))
                            else 'PU'
                          end constraint_type
                     from user_constraints       u
                    inner join user_cons_columns c
                       on c.table_name      = u.table_name
                      and c.constraint_name = u.constraint_name
                    where u.constraint_type in('U','P','u','p')
                    group by
                          c.table_name
                         ,c.column_name
                  ) p --プライマリキー
    on upper(p.table_name)  = upper(t.table_name)
   and upper(p.column_name) = upper(c.column_name)
  left outer join (select uic.table_name
                         ,uic.column_name
                         ,min(uic.column_position) as column_position
                     from
                          user_indexes     ui
                    inner join
                          user_ind_columns uic
                       on uic.index_name = ui.index_name
                    where
                          upper(ui.uniqueness) = 'UNIQUE'
                    group by
                          uic.table_name
                         ,uic.column_name
                  ) q --ユニークインデックス
    on q.table_name      = c.table_name
   and q.column_position = c.column_id
  left outer join (select distinct nvl(column_name,' ') as column_name
                         ,nvl(name,' ') as table_name
                     from dba_part_key_columns
                    where upper(object_type) = 'TABLE'
                  ) pt --パーティション表
    on pt.table_name  = c.table_name
   and pt.column_name = c.column_name
 where upper(o.owner) = 'スキーマ名'
-- and upper(c.table_name) = 'テーブル名'
 order by
       c.table_name
      ,c.column_id
;