DBA_TAB_COLS
DBA_TAB_COLSはDBA_TAB_COLUMNSと異なり、非表示列(HIDDEN_COLUMN=YES)も保持しています。普段から何かとテーブルのカラム情報を確認する事がありまして、毎回必要な項目を探して打ち込むのが大変なので2パターンほど投稿してみました。適当にアレンジして使ってください。
テーブルカラム一覧
取得パターン1
基本的にはdba_tab_colsの主要な項目をそのまま出力しています。エクセル等に貼り付けてテーブル定義の比較等によく利用します。
DBA_TAB_COLSからテーブルカラム一覧出力SQL
select
--c.OWNER,c.TABLE_NAME,
c.INTERNAL_COLUMN_ID -- 内部の保持順番
,c.COLUMN_NAME
,c.DATA_TYPE -- 型
,c.DATA_LENGTH -- データ長(バイト)
,CHAR_USED --
,CHAR_LENGTH --
,c.CHARACTER_SET_NAME --
,c.DATA_PRECISION --
,c.DATA_SCALE
,c.DEFAULT_ON_NULL -- DEFAULT ON NULLかどうか
,c.NULLABLE --
,c.DATA_DEFAULT
,c.HIDDEN_COLUMN -- 不可視カラムかどうか
,c.VIRTUAL_COLUMN -- 仮想列かどうか
,c.HISTOGRAM
,c.LAST_ANALYZED
from dba_tab_cols c where c.TABLE_NAME='EX200' and c.OWNER ='SOONI'
and c.USER_GENERATED='YES'
order by c.INTERNAL_COLUMN_ID
;
select
--c.OWNER,c.TABLE_NAME,
c.INTERNAL_COLUMN_ID -- 内部の保持順番
,c.COLUMN_NAME
,c.DATA_TYPE -- 型
,c.DATA_LENGTH -- データ長(バイト)
,CHAR_USED --
,CHAR_LENGTH --
,c.CHARACTER_SET_NAME --
,c.DATA_PRECISION --
,c.DATA_SCALE
,c.DEFAULT_ON_NULL -- DEFAULT ON NULLかどうか
,c.NULLABLE --
,c.DATA_DEFAULT
,c.HIDDEN_COLUMN -- 不可視カラムかどうか
,c.VIRTUAL_COLUMN -- 仮想列かどうか
,c.HISTOGRAM
,c.LAST_ANALYZED
from dba_tab_cols c where c.TABLE_NAME='EX200' and c.OWNER ='SOONI'
and c.USER_GENERATED='YES'
order by c.INTERNAL_COLUMN_ID
;
一覧出力例

SQL*Plus出力用
set lin 200
set pages 100
COLUMN INTERNAL_COLUMN_ID FORMAT 99
COLUMN INTERNAL_COLUMN_ID HEADING 'COLUMN|ID'
COLUMN COLUMN_NAME FORMAT A14
COLUMN DATA_TYPE FORMAT A10
COLUMN DATA_LENGTH FORMAT 99999
COLUMN DATA_LENGTH HEADING 'DATA|LENG'
COLUMN CHAR_USED HEADING 'CHAR|USED'
COLUMN CHAR_LENGTH FORMAT 99999
COLUMN CHAR_LENGTH HEADING 'CHAR|LENG'
COLUMN CHARACTER_SET_NAME FORMAT A8
COLUMN CHARACTER_SET_NAME HEADING 'CHAR.SET|NAME'
COLUMN DATA_PRECISION FORMAT 99999
COLUMN DATA_PRECISION HEADING 'PRECI|SION'
COLUMN DATA_SCALE FORMAT 99999
COLUMN DATA_SCALE HEADING 'DATA|SCALE'
COLUMN NULLABLE FORMAT A4
COLUMN NULLABLE HEADING 'NULL|ABLE'
COLUMN DATA_DEFAULT FORMAT A34
COLUMN DATA_DEFAULT HEADING 'DATA_DEFAULT'
COLUMN HISTOGRAM FORMAT A9
set lin 200
set pages 100
COLUMN INTERNAL_COLUMN_ID FORMAT 99
COLUMN INTERNAL_COLUMN_ID HEADING 'COLUMN|ID'
COLUMN COLUMN_NAME FORMAT A14
COLUMN DATA_TYPE FORMAT A10
COLUMN DATA_LENGTH FORMAT 99999
COLUMN DATA_LENGTH HEADING 'DATA|LENG'
COLUMN CHAR_USED HEADING 'CHAR|USED'
COLUMN CHAR_LENGTH FORMAT 99999
COLUMN CHAR_LENGTH HEADING 'CHAR|LENG'
COLUMN CHARACTER_SET_NAME FORMAT A8
COLUMN CHARACTER_SET_NAME HEADING 'CHAR.SET|NAME'
COLUMN DATA_PRECISION FORMAT 99999
COLUMN DATA_PRECISION HEADING 'PRECI|SION'
COLUMN DATA_SCALE FORMAT 99999
COLUMN DATA_SCALE HEADING 'DATA|SCALE'
COLUMN NULLABLE FORMAT A4
COLUMN NULLABLE HEADING 'NULL|ABLE'
COLUMN DATA_DEFAULT FORMAT A34
COLUMN DATA_DEFAULT HEADING 'DATA_DEFAULT'
COLUMN HISTOGRAM FORMAT A9
一覧出力対象テーブルのDDLの投入データは以下の通り
補足
INVISIBLEカラム(HIDDEN_COLUMN=YES)とDEFAULT ON NULLを定義した場合の動作を以下補足します。
取得パターン2
DATA_TYPEをCREATE TABLE文で利用する形式にてカラム一覧出力SQL
select
c.INTERNAL_COLUMN_ID -- 内部の保持順番
,c.COLUMN_NAME
,decode(NULLABLE,'N','NOT NULL',null) NULLABLE
,case
when c.DATA_TYPE='NUMBER' and c.DATA_PRECISION is null
then 'NUMBER'
when c.DATA_TYPE='NUMBER' and c.DATA_PRECISION is not null
then 'NUMBER('||c.DATA_PRECISION||decode(c.data_scale,0,')',','||c.DATA_SCALE||')')
when c.DATA_TYPE='VARCHAR2'
then 'VARCHAR2('||c.CHAR_LENGTH||decode(c.CHAR_USED,'B',')',' CHAR)')
when c.DATA_TYPE='CHAR'
then 'CHAR('||c.CHAR_LENGTH||decode(c.CHAR_USED,'B',')',' CHAR)')
when c.DATA_TYPE='NVARCHAR2' and c.CHAR_USED ='C'
then 'NVARCHAR2('||c.CHAR_LENGTH||')'
when c.DATA_TYPE='NCHAR' and c.CHAR_USED ='C'
then 'NCHAR('||c.CHAR_LENGTH||')'
when c.DATA_TYPE='RAW'
then 'RAW('||c.DATA_LENGTH||')'
when c.DATA_TYPE like 'TIMESTAMP%' and c.DATA_SCALE is not null
then c.DATA_TYPE
when c.DATA_TYPE in ('ROWID','CLOB','BLOB','LONG')
then c.DATA_TYPE
else c.DATA_TYPE
end DATA_TYPE
,c.HIDDEN_COLUMN -- 不可視カラムかどうか
,c.VIRTUAL_COLUMN -- 仮想列かどうか
,decode(c.DEFAULT_ON_NULL,'YES','ON NULL',null) DEFAULT_ON_NULL
,c.DATA_DEFAULT
from dba_tab_cols c where c.TABLE_NAME='EX200' and c.OWNER ='SOONI'
and c.USER_GENERATED='YES'
order by c.INTERNAL_COLUMN_ID
;
select
c.INTERNAL_COLUMN_ID -- 内部の保持順番
,c.COLUMN_NAME
,decode(NULLABLE,'N','NOT NULL',null) NULLABLE
,case
when c.DATA_TYPE='NUMBER' and c.DATA_PRECISION is null
then 'NUMBER'
when c.DATA_TYPE='NUMBER' and c.DATA_PRECISION is not null
then 'NUMBER('||c.DATA_PRECISION||decode(c.data_scale,0,')',','||c.DATA_SCALE||')')
when c.DATA_TYPE='VARCHAR2'
then 'VARCHAR2('||c.CHAR_LENGTH||decode(c.CHAR_USED,'B',')',' CHAR)')
when c.DATA_TYPE='CHAR'
then 'CHAR('||c.CHAR_LENGTH||decode(c.CHAR_USED,'B',')',' CHAR)')
when c.DATA_TYPE='NVARCHAR2' and c.CHAR_USED ='C'
then 'NVARCHAR2('||c.CHAR_LENGTH||')'
when c.DATA_TYPE='NCHAR' and c.CHAR_USED ='C'
then 'NCHAR('||c.CHAR_LENGTH||')'
when c.DATA_TYPE='RAW'
then 'RAW('||c.DATA_LENGTH||')'
when c.DATA_TYPE like 'TIMESTAMP%' and c.DATA_SCALE is not null
then c.DATA_TYPE
when c.DATA_TYPE in ('ROWID','CLOB','BLOB','LONG')
then c.DATA_TYPE
else c.DATA_TYPE
end DATA_TYPE
,c.HIDDEN_COLUMN -- 不可視カラムかどうか
,c.VIRTUAL_COLUMN -- 仮想列かどうか
,decode(c.DEFAULT_ON_NULL,'YES','ON NULL',null) DEFAULT_ON_NULL
,c.DATA_DEFAULT
from dba_tab_cols c where c.TABLE_NAME='EX200' and c.OWNER ='SOONI'
and c.USER_GENERATED='YES'
order by c.INTERNAL_COLUMN_ID
;
一覧出力例
SQL*Plus出力用
set lin 200
COLUMN COLUMN_NAME FORMAT A14
COLUMN NULLABLE FORMAT A8
COLUMN DATA_TYPE FORMAT A20
COLUMN HIDDEN_COLUMN FORMAT A3
COLUMN DATA_DEFAULT FORMAT A32
COLUMN INTERNAL_COLUMN_ID HEADING 'COLUMN|ID'
set lin 200
COLUMN COLUMN_NAME FORMAT A14
COLUMN NULLABLE FORMAT A8
COLUMN DATA_TYPE FORMAT A20
COLUMN HIDDEN_COLUMN FORMAT A3
COLUMN DATA_DEFAULT FORMAT A32
COLUMN INTERNAL_COLUMN_ID HEADING 'COLUMN|ID'