データ・ディクショナリ

Oracle カラム一覧(テーブルのカラム情報)

DBA_TAB_COLS

DBA_TAB_COLSはDBA_TAB_COLUMNSと異なり、非表示列(HIDDEN_COLUMN=YES)も保持しています。普段から何かとテーブルのカラム情報を確認する事がありまして、毎回必要な項目を探して打ち込むのが大変なので2パターンほど投稿してみました。適当にアレンジして使ってください。

テーブルカラム一覧
  1. 主要な項目をそのまま出力
  2. DATA_TYPEをCREATE TABLE文で利用する形式にして出力

取得パターン1

基本的にはdba_tab_colsの主要な項目をそのまま出力しています。エクセル等に貼り付けてテーブル定義の比較等によく利用します。

DBA_TAB_COLSからテーブルカラム一覧出力SQL
Plain text
Copy to clipboard
Open code in new window
EnlighterJS 3 Syntax Highlighter
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 ;
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出力用
Plain text
Copy to clipboard
Open code in new window
EnlighterJS 3 Syntax Highlighter
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
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の投入データは以下の通り
create table ex200 
(
  "AUTO_ID" NUMBER INVISIBLE GENERATED BY DEFAULT AS 
   IDENTITY MINVALUE 1 MAXVALUE 9999999999999999999999999999
   INCREMENT BY 1 START WITH 1000000000 CACHE 20 NOORDER  NOCYCLE  NOT NULL ENABLE
,c1 number default on null 999
,c2 number(3,2) not null
,c3 varchar2(10 char)  -- charを指定すると文字数となります。
,c4 date default sysdate
,c5 nvarchar2(10)   
);

insert into EX200 (C1, C2, C3, C4, C5) values(null,4,'コーヒー',null,'コーヒー');
insert into EX200 (C1, C2, C3,     C5) values(100,1.42,'お茶漬け','お茶漬け');
補足

INVISIBLEカラム(HIDDEN_COLUMN=YES)とDEFAULT ON NULLを定義した場合の動作を以下補足します。

-- default on null 999 と定義したC1カラムは、NULLを指定した場合、999が自動で入っています。
-- またAUTO_IDはINVISIBLEとしてあるので、select * from では表示されない点注意が必要です。
--
SQL> select * from ex200
  2  ;

        C1         C2 C3                   C4                  C5
---------- ---------- -------------------- ------------------- --------------------
       999          4 コーヒー                                 コーヒー
       100       1.42 お茶漬け             2021-12-28 20:13:40 お茶漬け

--
-- INVISIBLEカラムは指定した場合のみ出力されます
--
SQL> select AUTO_ID,C1, C2, C3, C4, C5 from EX200  order by 1
  2  ;

   AUTO_ID         C1         C2 C3                   C4                  C5
---------- ---------- ---------- -------------------- ------------------- --------------------
1000000000        999          4 コーヒー                                 コーヒー
1000000001        100       1.42 お茶漬け             2021-12-28 20:13:40 お茶漬け

SQL>

取得パターン2

DATA_TYPEをCREATE TABLE文で利用する形式にてカラム一覧出力SQL
Plain text
Copy to clipboard
Open code in new window
EnlighterJS 3 Syntax Highlighter
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 ;
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
;
一覧出力例

    COLUMN
        ID COLUMN_NAME    NULLABLE DATA_TYPE            HID VIRTUA DEFAULT_ON_NUL DATA_DEFAULT
---------- -------------- -------- -------------------- --- ------ -------------- --------------------------------
         1 AUTO_ID        NOT NULL NUMBER               YES NO                    "SOONI"."ISEQ$$_73520".nextval
         2 C1             NOT NULL NUMBER               NO  NO     ON NULL        999
         3 C2             NOT NULL NUMBER(3,2)          NO  NO
         4 C3                      VARCHAR2(10 CHAR)    NO  NO
         5 C4                      DATE                 NO  NO                    sysdate
         6 C5                      NVARCHAR2(10)        NO  NO

6行が選択されました。

SQL>
SQL*Plus出力用
Plain text
Copy to clipboard
Open code in new window
EnlighterJS 3 Syntax Highlighter
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'
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'

カラムコメントも出力する場合はこちらも参照

スポンサーリンク
タイトルとURLをコピーしました