-- desc.sql
-- INDEXの有無（PK / not PK）を表示
-- Usage :
--        SQL> @desc テーブル名
--
col 列名 format a26
col null format a4
col データ型 format a13
col "index(PK)" format a9
col "index(Not PK)" format a13

select
tc.column_name    "列名",
decode(nvl(tc.data_scale,0),0, --小数点なしか
  --小数点なし→整数か
    decode(tc.data_type,'NUMBER',
      --整数
        tc.data_type || '(' || ltrim(to_char(tc.data_precision)) || ')',
      --整数以外→VARCHAR2か
        decode(tc.data_type,'VARCHAR2',
        --VARCHAR2
          tc.data_type || '(' || ltrim(to_char(tc.data_length)) || ')',
        --VARCHAR2以外→CHARか
          decode(tc.data_type,'CHAR',
          --CHAR
            tc.data_type || '(' || ltrim(to_char(tc.data_length)) || ')',
          --それ以外
            tc.data_type
          )
        )
    ),
  --小数点あり
    tc.data_type || '(' || ltrim(to_char(tc.data_precision)) || ',' || ltrim(to_char(tc.data_scale)) || ')'
) "データ型",
decode(tc.nullable,'Y','  ','No') "Null",
decode(mod(v.v_isPrim,2),1,'○',null) "index(PK)",
decode(greatest(nvl(v.v_isPrim,0),1),1,null,'○') "index(Not PK)"
from user_tab_columns tc,
(
 select ic.column_name as v_column_name,
 sum(decode (ic.index_name,c.constraint_name,1,2)) as v_isPrim
 from user_ind_columns ic, user_constraints c, user_indexes i
 where ic.table_name = c.table_name (+)
 and ic.index_name = i.index_name (+)
 and 'P' = c.constraint_type (+)
 and c.status = 'ENABLED'
 and ic.table_name = upper('&&1')
 and c.table_name (+) = upper('&&1')
 group by ic.column_name
) v
where tc.table_name = upper('&&1')
and tc.column_name = v.v_column_name (+)
order by tc.column_id
;