DBA_TAB_COLS
DBA_TAB_COLSはDBA_TAB_COLUMNSと異なり、非表示列(HIDDEN_COLUMN=YES)も保持しています。普段から何かとテーブルのカラム情報を確認する事がありまして、毎回必要な項目を探して打ち込むのが大変なので２パターンほど投稿してみました。適当にアレンジして使ってください。
テーブルカラム一覧
取得パターン１
基本的には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 ;
一覧出力例
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
一覧出力対象テーブルの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>取得パターン２
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
;
一覧出力例
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出力用
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'
