tabind.sql
テーブルに紐づくインデックス一覧を表示する
■使用法
SQL> @tabind <テーブル名-前方一致>
code:SQL
--@tabind <TBLNM-前方一致>
define TBLNM=&1
set verify off
set timing off
@column_format
--set sqlformat ansiconsole
break on INDEX_NAME skip page
select distinct
I.OWNER INDEX_OWNER
,case when I.OWNER = sys_context('userenv','current_schema') then '*'
else null
end OWN
,I.TABLE_NAME
--,T.COMMENTS TAB_NAME
,I.INDEX_NAME
,C.COLUMN_NAME
--,O.COMMENTS COL_NAME
,C.COLUMN_POSITION
,I.PARTITIONED
,I.VISIBILITY
,I.INDEX_TYPE
,case when I.UNIQUENESS = 'UNIQUE' then 'UNIQUE'
else null
end UQ
from
DBA_IND_COLUMNS C
,DBA_INDEXES I
where I.TABLE_NAME = C.TABLE_NAME
and I.INDEX_NAME = C.INDEX_NAME
and I.OWNER = C.INDEX_OWNER
and I.TABLE_NAME like upper('&TBLNM%')
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 TBLNM
set verify on
@default_settings
--set sqlformat default
■例
SQL> @tabind job
INDEX_OWNER O TABLE_NAME INDEX_NAME COLUMN_NAME COLUMN_POSITION PARTIT VISIBILITY INDEX_TYPE UQ
------------ - ------------------------- ------------------------------ ------------------------- --------------- ------ ---------- ---------- ------
HR * JOBS JOB_ID_PK JOB_ID 1 NO VISIBLE NORMAL UNIQUE
INDEX_OWNER O TABLE_NAME INDEX_NAME COLUMN_NAME COLUMN_POSITION PARTIT VISIBILITY INDEX_TYPE UQ
------------ - ------------------------- ------------------------------ ------------------------- --------------- ------ ---------- ---------- ------
HR * JOB_HISTORY JHIST_DEPARTMENT_IX DEPARTMENT_ID 1 NO VISIBLE NORMAL
INDEX_OWNER O TABLE_NAME INDEX_NAME COLUMN_NAME COLUMN_POSITION PARTIT VISIBILITY INDEX_TYPE UQ
------------ - ------------------------- ------------------------------ ------------------------- --------------- ------ ---------- ---------- ------
HR * JOB_HISTORY JHIST_EMPLOYEE_IX EMPLOYEE_ID 1 NO VISIBLE NORMAL
INDEX_OWNER O TABLE_NAME INDEX_NAME COLUMN_NAME COLUMN_POSITION PARTIT VISIBILITY INDEX_TYPE UQ
------------ - ------------------------- ------------------------------ ------------------------- --------------- ------ ---------- ---------- ------
HR * JOB_HISTORY JHIST_EMP_ID_ST_DATE_PK EMPLOYEE_ID 1 NO VISIBLE NORMAL UNIQUE
HR * JOB_HISTORY START_DATE 2 NO VISIBLE NORMAL UNIQUE
INDEX_OWNER O TABLE_NAME INDEX_NAME COLUMN_NAME COLUMN_POSITION PARTIT VISIBILITY INDEX_TYPE UQ
------------ - ------------------------- ------------------------------ ------------------------- --------------- ------ ---------- ---------- ------
HR * JOB_HISTORY JHIST_JOB_IX JOB_ID 1 NO VISIBLE NORMAL
6行が選択されました。