list_indexes.sql
--
-- インデックス一覧
--
-- history
-- 2018/02/24 ORDER BY句の位置を修正
-- 2018/03/03 INDEX_TYPEの表示位置を修正
--
SET ECHO OFF
SET FEEDBACK OFF
SET LINESIZE 500
SET PAGESIZE 50000
SET TERMOUT OFF
SET TRIMSPOOL ON
-- SPOOL pass includes_CONNECT_IDENTIFIER and SYSDATE
COLUMN SYSTEM_DATE NEW_VALUE SYSTEM_DATE
SELECT '_' || TO_CHAR(SYSDATE, 'YYYYMMDD_HH24MISS') AS SYSTEM_DATE FROM DUAL ;
SPOOL list_indexes_&&_CONNECT_IDENTIFIER.&&SYSTEM_DATE..log
-- Windows ↑, UNIX/Linux では ↓ を有効に
-- SPOOL list_indexes_\&&_CONNECT_IDENTIFIER.\&&SYSTEM_DATE..log
BREAK ON TABLE_NAME ON INDEX_NAME ON CONSTRAINT_TYPE
COLUMN TABLE_NAME FORMAT A30
COLUMN INDEX_TYPE FORMAT A30
COLUMN INDEX_NAME FORMAT A30
COLUMN CONSTRAINT_TYPE FORMAT A21
COLUMN COLUMN_NAMES FORMAT A300
SELECT DISTINCT
A.TABLE_NAME
, A.INDEX_TYPE
, A.INDEX_NAME
, A.CONSTRAINT_TYPE
, NVL2(A.COLUMN_NAMES, '(' || A.COLUMN_NAMES || ')', NULL) AS COLUMN_NAMES
FROM(
SELECT
TA.TABLE_NAME
, IX.INDEX_TYPE
, NVL(IX.INDEX_NAME, '#no index#') AS INDEX_NAME
, CASE CN.CONSTRAINT_TYPE
WHEN 'C' THEN 'CONSTRAINT' -- 表に対する検査制約
WHEN 'P' THEN 'PRIMARY KEY' -- 主キー
WHEN 'U' THEN 'UNIQUE' -- 一意のキー
WHEN 'R' THEN 'REFERENCES' -- 参照整合性
WHEN 'V' THEN 'WITH CHECK' -- ビューでのチェック・オプション付き
WHEN 'O' THEN 'READ ONLY' -- ビューでの読取り専用
ELSE
CASE
WHEN IX.UNIQUENESS = 'UNIQUE INDEX' THEN
IX.UNIQUENESS
END
END AS CONSTRAINT_TYPE
, LISTAGG(IC.COLUMN_NAME || DECODE(IC.DESCEND, 'DESC', ' DESC'), ', ')
WITHIN GROUP(ORDER BY IC.COLUMN_POSITION)
OVER(PARTITION BY TA.TABLE_NAME, IX.INDEX_NAME) AS COLUMN_NAMES
FROM USER_TABLES TA
LEFT OUTER JOIN USER_INDEXES IX -- 索引のないテーブルもある
ON IX.TABLE_NAME = TA.TABLE_NAME
INNER JOIN USER_IND_COLUMNS IC
ON IC.TABLE_NAME = IX.TABLE_NAME
AND IC.INDEX_NAME = IX.INDEX_NAME
LEFT OUTER JOIN USER_CONSTRAINTS CN -- 制約のないテーブルもある
ON CN.TABLE_NAME = IX.TABLE_NAME
AND CN.CONSTRAINT_NAME = IX.INDEX_NAME
WHERE
TA.DROPPED = 'NO' -- except trash box, since oracle10.1.0
AND TA.TABLE_NAME NOT IN
(
'CHAINED_ROWS' -- except Oracle side table
, 'EXCEPTIONS'
, 'PLAN_TABLE'
, 'PLAN_TABLE$'
)
) A
ORDER BY
A.TABLE_NAME
, A.INDEX_NAME ;
SPOOL OFF
CLEAR BREAKS
COLUMN TABLE_NAME CLEAR
COLUMN INDEX_NAME CLEAR
COLUMN CONSTRAINT_TYPE CLEAR
SET ECHO ON
SET FEEDBACK ON
SET LINESIZE 80