162 lines
6.5 KiB
MySQL
162 lines
6.5 KiB
MySQL
@@header
|
|
|
|
/*
|
|
*
|
|
* Author : Vishal Gupta
|
|
* Purpose : Display Tables indexes
|
|
* Parameters : 1 - Owner (Use % as wild card, \ as ESCAPE)
|
|
* 2 - TableName (Use % as wild card, \ as ESCAPE)
|
|
*
|
|
*
|
|
* Revision History:
|
|
* ===================
|
|
* Date Author Description
|
|
* --------- ------------ -----------------------------------------
|
|
* 15-Mar-12 Vishal Gupta First Draft
|
|
* 12-Jun-12 Vishal Gupta Added indexes status to output
|
|
*/
|
|
|
|
/************************************
|
|
* INPUT PARAMETERS
|
|
************************************/
|
|
UNDEFINE owner
|
|
UNDEFINE table_name
|
|
DEFINE owner="&&1"
|
|
DEFINE table_name="&&2"
|
|
|
|
COLUMN _owner NEW_VALUE owner NOPRINT
|
|
COLUMN _table_name NEW_VALUE table_name NOPRINT
|
|
COLUMN _object_type NEW_VALUE object_type NOPRINT
|
|
|
|
|
|
set term off
|
|
SELECT SUBSTR(UPPER('&&owner'), 1 , CASE INSTR('&&owner','.') WHEN 0 THEN LENGTH ('&&owner') ELSE INSTR('&&owner','.') - 1 END) "_owner"
|
|
, CASE
|
|
WHEN INSTR('&&owner','.') != 0 THEN SUBSTR(UPPER('&&owner'),INSTR('&&owner','.')+1)
|
|
ELSE DECODE(UPPER('&&table_name'),'','%',UPPER('&&table_name'))
|
|
END "_table_name"
|
|
FROM DUAL;
|
|
set term on
|
|
|
|
Prompt
|
|
Prompt ***********************************************
|
|
Prompt * Table Indexes
|
|
PROMPT *
|
|
PROMPT * Input Parameters
|
|
PROMPT * - Table Owner = '&&owner'
|
|
PROMPT * - Table Name = '&&table_name'
|
|
Prompt ***********************************************
|
|
|
|
|
|
COLUMN table_name HEADING "Table Name" FORMAT a45
|
|
COLUMN index_name HEADING "Index Name" FORMAT a45
|
|
COLUMN index_type HEADING "Index Type" FORMAT a10 TRUNC
|
|
COLUMN status HEADING "Status" FORMAT a8
|
|
COLUMN visibility HEADING "Visible" FORMAT a10
|
|
COLUMN uniqueness HEADING "Uniqueness" FORMAT a15
|
|
COLUMN Columns HEADING "Index columns" FORMAT a90
|
|
|
|
BREAK ON TABLE_NAME
|
|
|
|
select /*+ */
|
|
i.table_owner || '.' || i.table_name table_name
|
|
, i.index_owner || '.' || i.index_name index_name
|
|
, i.uniqueness
|
|
, i.index_type
|
|
, i.status
|
|
, i.visibility
|
|
, SUBSTR(SYS_CONNECT_BY_PATH ( NVL(i.column_expression,i.column_name) || DECODE(i.descend,'ASC','',' DESC') , ', ' ),3) Columns
|
|
from
|
|
xmltable( '/ROWSET/ROW'
|
|
passing dbms_xmlgen.getXMLType('
|
|
select i.table_owner
|
|
, i.table_name
|
|
, i.owner index_owner
|
|
, i.index_name
|
|
, i.status
|
|
, i.uniqueness
|
|
, i.index_type
|
|
, i.visibility
|
|
, ic.column_name
|
|
, ic.descend
|
|
, ic.column_position column_position
|
|
, ie.column_expression /* <---- Long Column type */
|
|
, ie.column_position column_position_exp
|
|
FROM dba_indexes i
|
|
JOIN dba_ind_columns ic
|
|
ON i.owner = ic.index_owner
|
|
AND i.index_name = ic.index_name
|
|
AND i.table_owner = ic.table_owner
|
|
AND i.table_name = ic.table_name
|
|
LEFT OUTER JOIN dba_ind_expressions ie
|
|
ON ic.index_owner = ie.index_owner
|
|
AND ic.index_name = ie.index_name
|
|
AND ic.table_owner = ie.table_owner
|
|
AND ic.table_name = ie.table_name
|
|
AND ic.column_position = ie.column_position
|
|
WHERE 1=1
|
|
AND i.table_owner LIKE upper(''&&owner'') ESCAPE ''\''
|
|
AND i.table_name LIKE upper(''&&table_name'') ESCAPE ''\''
|
|
')
|
|
columns
|
|
table_owner varchar2(30)
|
|
, table_name varchar2(30)
|
|
, index_owner varchar2(30)
|
|
, index_name varchar2(30)
|
|
, status varchar2(30)
|
|
, uniqueness VARCHAR2(9)
|
|
, index_type VARCHAR2(27)
|
|
, visibility VARCHAR2(9)
|
|
, column_name varchar2(30)
|
|
, descend varchar2(30)
|
|
, column_position number
|
|
, column_expression varchar2(4000)
|
|
, column_position_exp number
|
|
) i
|
|
WHERE 1=1
|
|
AND CONNECT_BY_ISLEAF = 1
|
|
AND LEVEL = NVL(i.column_position_exp,i.column_position)
|
|
CONNECT BY i.table_owner = PRIOR i.table_owner
|
|
AND i.table_name = PRIOR i.table_name
|
|
AND i.index_owner = PRIOR i.index_owner
|
|
AND i.index_name = PRIOR i.index_name
|
|
AND NVL(i.column_position_exp,i.column_position) - 1 = PRIOR NVL(i.column_position_exp,i.column_position)
|
|
ORDER BY i.table_owner
|
|
, i.table_name
|
|
, i.index_owner
|
|
, i.index_name
|
|
;
|
|
|
|
/*
|
|
SELECT
|
|
'SYS' || '.' || ic.table_name table_name
|
|
, 'SYS' || '.' || ic.index_name index_name
|
|
, i.uniqueness
|
|
, i.index_type
|
|
, i.status
|
|
, i.visibility
|
|
-- , column_name
|
|
-- , column_position
|
|
-- , CONNECT_BY_ISLEAF
|
|
-- , LEVEL
|
|
, SUBSTR(SYS_CONNECT_BY_PATH ( ic.column_name || DECODE(ic.descend,'ASC','',' (desc) ') , ' , ' ),4) Columns
|
|
FROM v$indexed_fixed_column ic
|
|
WHERE 'SYS' LIKE upper('&&owner') ESCAPE '\'
|
|
AND ic.table_name LIKE upper('&&table_name') ESCAPE '\'
|
|
AND CONNECT_BY_ISLEAF = 1
|
|
AND LEVEL = ic.column_position
|
|
CONNECT BY ic.table_name = PRIOR ic.table_name
|
|
AND ic.index_number = PRIOR ic.index_number
|
|
AND ic.column_position - 1 = PRIOR ic.column_position
|
|
START WITH ic.table_name LIKE upper('&&table_name') ESCAPE '\'
|
|
ORDER BY ic.table_owner
|
|
, ic.table_name
|
|
, ic.index_owner
|
|
, i.uniqueness
|
|
, ic.index_name
|
|
;
|
|
*/
|
|
|
|
|
|
@@footer
|