【Oracle データベース】カラム定義確認用SQL

はじめに

テーブルのカラム定義は ALL_TAB_COLUMNS や USER_TAB_COLUMNS を見ます。

しかし、データタイプ毎にデータ長の捉え方が異なるため、きれいに一覧にするのは結構シンドイです。

ここでは、SQL Developer がカラム定義一覧を取得しているSQLを紹介します。

テーブルのカラム定義一覧

SQL Developer のテーブル カラム定義一覧では以下のSQLが発行されています。

select * from (
  select
    c.column_name,
    case when data_type = 'CHAR' then data_type||'('||c.char_length||decode(char_used,'B',' BYTE','C',' CHAR',null)||')'
      when data_type = 'VARCHAR' then data_type||'('||c.char_length||decode(char_used,'B',' BYTE','C',' CHAR',null)||')'
      when data_type = 'VARCHAR2' then data_type||'('||c.char_length||decode(char_used,'B',' BYTE','C',' CHAR',null)||')'
      when data_type = 'NCHAR' then data_type||'('||c.char_length||decode(char_used,'B',' BYTE','C',' CHAR',null)||')'
      when data_type = 'NUMBER' then
        case when c.data_precision is null and c.data_scale is null then 'NUMBER'
          when c.data_precision is null and c.data_scale is not null then 'NUMBER(38,'||c.data_scale||')'
          else data_type||'('||c.data_precision||','||c.data_SCALE||')'
        end
      when data_type = 'NVARCHAR' then data_type||'('||c.char_length||decode(char_used,'B',' BYTE','C',' CHAR',null)||')'
      when data_type = 'NVARCHAR2' then data_type||'('||c.char_length||decode(char_used,'B',' BYTE','C',' CHAR',null)||')'
      else data_type
    end data_type,
    decode(nullable,'Y','Yes','No') nullable,
    c.DATA_DEFAULT,
    column_id,
    com.comments
  from sys.all_tab_columns c, 
    sys.all_col_comments com
  where c.owner = :OBJECT_OWNER
    and c.table_name = :OBJECT_NAME
    and c.table_name = com.table_name
    and c.owner = com.owner
    and c.column_name = com.column_name
  order by column_id
) sub1 order by 1 asc

自力で書くのは、まぁ無理ですね。

USER_TAB_COLUMNS から取得する場合には以下のようにすることができます。

select * from (
  select
    column_id,
    c.column_name,
    case when data_type = 'CHAR' then data_type||'('||c.char_length||decode(char_used,'B',' BYTE','C',' CHAR',null)||')'
      when data_type = 'VARCHAR' then data_type||'('||c.char_length||decode(char_used,'B',' BYTE','C',' CHAR',null)||')'
      when data_type = 'VARCHAR2' then data_type||'('||c.char_length||decode(char_used,'B',' BYTE','C',' CHAR',null)||')'
      when data_type = 'NCHAR' then data_type||'('||c.char_length||decode(char_used,'B',' BYTE','C',' CHAR',null)||')'
      when data_type = 'NUMBER' then
        case when c.data_precision is null and c.data_scale is null then 'NUMBER'
          when c.data_precision is null and c.data_scale is not null then 'NUMBER(38,'||c.data_scale||')'
          else data_type||'('||c.data_precision||','||c.data_SCALE||')'
        end
      when data_type = 'NVARCHAR' then data_type||'('||c.char_length||decode(char_used,'B',' BYTE','C',' CHAR',null)||')'
      when data_type = 'NVARCHAR2' then data_type||'('||c.char_length||decode(char_used,'B',' BYTE','C',' CHAR',null)||')'
      else data_type
    end data_type,
    decode(nullable,'Y','Yes','No') nullable,
    c.DATA_DEFAULT,
    com.comments
  from user_tab_columns c,
    user_col_comments com
  where c.table_name = :OBJECT_NAME
    and c.table_name = com.table_name
    and c.column_name = com.column_name
  order by column_id
) sub1 order by 1 asc"

ビューのカラム定義一覧

SQL Developer のビュー カラム定義一覧では以下のSQLが発行されています。

select
  c.column_name,
  UPPER (c.data_type) ||
    case when (c.data_type='VARCHAR' OR c.data_type = 'VARCHAR2' OR c.data_type='CHAR') AND ( c.data_length <> 0 AND  nvl(c.data_length,-1) <> -1) then
      case when(c.char_used ='C' and 'BYTE' =(select value from nls_session_parameters where PARAMETER='NLS_LENGTH_SEMANTICS')) then '(' || c.char_length || ' CHAR)'
        when(c.char_used ='B' and 'CHAR' =(select value from nls_session_parameters where PARAMETER='NLS_LENGTH_SEMANTICS')) then '(' || c.data_length || ' BYTE)'
        when(c.char_used ='C' and 'CHAR' =(select value from nls_session_parameters where PARAMETER='NLS_LENGTH_SEMANTICS')) then '(' || c.char_length || ')'
        when(c.char_used ='B' and 'BYTE' =(select value from nls_session_parameters where PARAMETER='NLS_LENGTH_SEMANTICS')) then '(' || c.data_length || ')'
        else '(' || c.data_length || ' BYTE)'
      end
      when (c.data_type='RAW') then ''
      when (c.data_type='NVARCHAR2' OR c.data_type='NCHAR') then '(' || c.data_length/2 || ')'
      when (c.data_type like 'TIMESTAMP%' OR c.data_type like 'INTERVAL DAY%' OR c.data_type like 'INTERVAL YEAR%' OR c.data_type = 'DATE' OR
        (c.data_type = 'NUMBER' AND ((c.data_precision = 0) OR NVL (c.data_precision,-1) = -1) AND  nvl (c.data_scale,-1) = -1)) then ''
      when ((c.data_type = 'NUMBER' AND NVL (c.data_precision,-1) = -1) AND (c.data_scale = 0)) then '(38)'
      when ((c.data_type = 'NUMBER' AND NVL (c.data_precision,-1) = -1) AND (nvl (c.data_scale,-1) != -1)) then '(38,'|| c.data_scale ||')'
      when (c.data_scale  = 0 OR nvl(c.data_scale,-1) = -1) then '('|| c.data_precision ||')'
      else '('|| c.data_precision ||',' ||c.data_scale ||')'
    end data_type,
  decode(nullable,'Y','Yes','No') nullable,
  c.DATA_DEFAULT,
  column_id, 
  com.comments,
  c_update.insertable,
  c_update.updatable,
  c_update.deletable
from sys.all_tab_columns c,
  sys.all_col_comments com,
  sys.all_updatable_columns c_update
where c.owner = :OBJECT_OWNER
 and c.table_name = :OBJECT_NAME
 and c.table_name = com.table_name
 and c.owner = com.owner
 and c.column_name = com.column_name
 and c_update.column_name = com.column_name
 and c_update.table_name = com.table_name
 and c_update.owner = com.owner

テーブルのカラム一覧とは、取得項目や桁数の表示形式が異なります。

USER_TAB_COLUMNS から取得する場合には以下のようにすることができます。

select
  c.column_id,
  c.column_name,
  UPPER (c.data_type) ||
    case
      when (c.data_type='VARCHAR' OR c.data_type = 'VARCHAR2' OR c.data_type='CHAR') AND (c.data_length <> 0 AND nvl(c.data_length,-1) <> -1) then
        case when(c.char_used ='C' and 'BYTE' =(select value from nls_session_parameters where PARAMETER='NLS_LENGTH_SEMANTICS')) then '(' || c.char_length || ' CHAR)'
          when(c.char_used ='B' and 'CHAR' =(select value from nls_session_parameters where PARAMETER='NLS_LENGTH_SEMANTICS')) then '(' || c.data_length || ' BYTE)'
          when(c.char_used ='C' and 'CHAR' =(select value from nls_session_parameters where PARAMETER='NLS_LENGTH_SEMANTICS')) then '(' || c.char_length || ')'
          when(c.char_used ='B' and 'BYTE' =(select value from nls_session_parameters where PARAMETER='NLS_LENGTH_SEMANTICS')) then '(' || c.data_length || ')'
          else '(' || c.data_length || ' BYTE)'
        end
      when (c.data_type='RAW') then ''
      when (c.data_type='NVARCHAR2' OR c.data_type='NCHAR') then '(' || c.data_length/2 || ')'
      when (c.data_type like 'TIMESTAMP%' OR c.data_type like 'INTERVAL DAY%' OR c.data_type like 'INTERVAL YEAR%' OR c.data_type = 'DATE' OR
           (c.data_type = 'NUMBER' AND ((c.data_precision = 0) OR NVL (c.data_precision,-1) = -1) AND  nvl (c.data_scale,-1) = -1)) then ''
      when ((c.data_type = 'NUMBER' AND NVL (c.data_precision,-1) = -1) AND (c.data_scale = 0)) then '(38)'
      when ((c.data_type = 'NUMBER' AND NVL (c.data_precision,-1) = -1) AND (nvl (c.data_scale,-1) != -1)) then '(38,'|| c.data_scale ||')'
      when (c.data_scale  = 0 OR nvl(c.data_scale,-1) = -1) then '('|| c.data_precision ||')'
      else '('|| c.data_precision ||',' ||c.data_scale ||')'
    end data_type,
    decode(nullable,'Y','Yes','No') nullable,
    com.comments
from user_tab_columns c, user_col_comments com
where c.table_name = :OBJECT_NAME
and c.table_name = com.table_name
and c.column_name = com.column_name
order by c.column_id";

まとめ

カラム定義の一覧を、雑に取得するSQLはすぐに見つかりますが、それなりの一覧にするSQLはなかなか出てこないので、ここにメモしておきました。