tabindcol.sql
テーブルとカラムに紐づくインデックス一覧を表示する
■使用法
SQL> @tabindcol <テーブル名-前方一致> <カラム名-完全一致>
code:SQL
--@tabindcol <TBLNM-前方一致> <COLNM-完全一致>
define TBLNM=&1
define COLNM=&2
set verify off
@column_format
break on INDEX_NAME skip 1
select distinct
I.OWNER INDEX_OWNER
,case when I.OWNER = sys_context('userenv','current_schema') then '*'
else null
end OWN
,I.TABLE_NAME
,I.INDEX_NAME
,case when C.COLUMN_NAME = upper('&COLNM') then '*'
else null
end IC
,C.COLUMN_NAME
,C.COLUMN_POSITION
,I.PARTITIONED
,case when I.UNIQUENESS = 'UNIQUE' then 'UNIQUE'
else null
end UQ
,I.VISIBILITY
,I.INDEX_TYPE
,T.NUM_DISTINCT
from
ALL_IND_COLUMNS C
,ALL_INDEXES I
,ALL_TAB_COLUMNS T
where I.TABLE_OWNER = C.TABLE_OWNER
and I.TABLE_NAME = C.TABLE_NAME
and I.INDEX_NAME = C.INDEX_NAME
and I.TABLE_NAME like upper('&TBLNM%')
and I.INDEX_NAME in (select C1.INDEX_NAME from ALL_IND_COLUMNS C1 where C1.TABLE_NAME like upper('&TBLNM%') and C1.COLUMN_NAME = upper('&COLNM'))
and I.TABLE_OWNER = T.OWNER
and C.TABLE_NAME = T.TABLE_NAME
and C.COLUMN_NAME = T.COLUMN_NAME
--and I.OWNER = sys_context('userenv','current_schema')
order by
I.OWNER
,I.TABLE_NAME
,I.INDEX_NAME
,C.COLUMN_POSITION;
set lines 80
undefine 1 2 TBLNM COLNM
set verify on
■使用例
SQL> @tabindcol job job_id
INDEX_OWNER O TABLE_NAME INDEX_NAME I COLUMN_NAME COLUMN_POSITION PARTIT UQ VISIBILITY INDEX_TYPE NUM_DISTINCT
------------ - -------------- ------------------------------ - ------------ --------------- ------ ------ ---------- ---------- ------------
HR * JOBS JOB_ID_PK * JOB_ID 1 NO UNIQUE VISIBLE NORMAL 19
HR * JOB_HISTORY JHIST_JOB_IX * JOB_ID 1 NO VISIBLE NORMAL 8