Re: List table's indexes !

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Tue, 18 Jan 2000 16:40:42 -0500
Message-ID: <cbn98skv6uqp7ja9f08d3dfpelplpbadfl@4ax.com>

A copy of this was sent to Pascal-Eric Servais <servpas_at_cognicase.ca> (if that email address didn't require changing) On Tue, 18 Jan 2000 20:56:12 GMT, you wrote:

>Hi !
>I am a little green with Oracle DB and working on a "rush" project !
>(same as usual ;)
>
>I just can't figure out how to list all the indexes that are
>associated to an Oracle table with SQL*Plus.
>Is anybody could help about that.
>
>BTW : If you are aware of any link to good online manuals, like some
>"Getting started with Oracle", I'd appreciate very much.
>
>Thanks !
>
>Pascal-Eric.
>
>
>Sent via Deja.com http://www.deja.com/
>Before you buy.

this script might help you out. save it to a file called 'desc'. Then you can do something like:

ops$tkyte_at_8i> @desc emp
Datatypes for Table emp

                               Data                           Data
Column Name                    Type                           Length

Nullable

------------------------------ ------------------------------ --------- ----
EMPNO                          NUMBER                         (4,0)     not null
ENAME                          VARCHAR2                       10        null
JOB                            VARCHAR2                       9         null
MGR                            NUMBER                         (4,0)     null
HIREDATE                       DATE                           7         null
SAL                            NUMBER                         (7,2)     null
COMM                           NUMBER                         (7,2)     null
DEPTNO                         NUMBER                         (2,0)     null
CATEGORY                       VARCHAR2                       30        null

Indexes on emp

Index                          Is
Name                           Unique Indexed Columns
------------------------------ ------ -----------------------------------
EMP_IDX1                       No     SAL
EMP_PK                         Yes    EMPNO




--------------------------------- script desc.sql ---------------------------

set verify off
set pagesize 9999
set feedback off

variable owner varchar2(30)
variable tname varchar2(30)
begin
  :owner := USER;
  :tname := upper('&1');
end;
/

Prompt Datatypes for Table &1

column data_type format a30

column column_name heading "Column Name"
column data_type   heading "Data|Type"
column data_length heading "Data|Length"
column nullable    heading "Nullable"

select column_name,
       data_type,
       substr(
       decode( data_type, 'NUMBER',
               decode( data_precision, NULL, NULL,
                '('||data_precision||','||data_scale||')' ), data_length),
              1,11) data_length,
       decode( nullable, 'Y', 'null', 'not null' ) nullable

from all_tab_columns
where owner = :owner
  and table_name = :tname
order by column_id
/

prompt
prompt
Prompt Indexes on &1

column index_name heading "Index|Name"
column Uniqueness heading "Is|Unique" format a6
column columns    heading "Indexed Columns" format a35 word_wrapped

select substr(a.index_name,1,30) index_name,
       decode(a.uniqueness,'UNIQUE','Yes','No') uniqueness,
max(decode( b.column_position,  1, substr(b.column_name,1,30), NULL )) ||
max(decode( b.column_position,  2, ', '||substr(b.column_name,1,30), NULL )) ||
max(decode( b.column_position,  3, ', '||substr(b.column_name,1,30), NULL )) ||
max(decode( b.column_position,  4, ', '||substr(b.column_name,1,30), NULL )) ||
max(decode( b.column_position,  5, ', '||substr(b.column_name,1,30), NULL )) ||
max(decode( b.column_position,  6, ', '||substr(b.column_name,1,30), NULL )) ||
max(decode( b.column_position,  7, ', '||substr(b.column_name,1,30), NULL )) ||
max(decode( b.column_position,  8, ', '||substr(b.column_name,1,30), NULL )) ||
max(decode( b.column_position,  9, ', '||substr(b.column_name,1,30), NULL )) ||
max(decode( b.column_position, 10, ', '||substr(b.column_name,1,30), NULL )) ||
max(decode( b.column_position, 11, ', '||substr(b.column_name,1,30), NULL )) ||
max(decode( b.column_position, 12, ', '||substr(b.column_name,1,30), NULL )) ||
max(decode( b.column_position, 13, ', '||substr(b.column_name,1,30), NULL )) ||
max(decode( b.column_position, 14, ', '||substr(b.column_name,1,30), NULL )) ||

max(decode( b.column_position, 15, ', '||substr(b.column_name,1,30), NULL )) || max(decode( b.column_position, 16, ', '||substr(b.column_name,1,30), NULL )) columns
from all_indexes a, all_ind_columns b
where a.owner = :owner

and a.table_name = :tname
and b.table_name = a.table_name
and b.table_owner = a.owner
and a.index_name = b.index_name

group by substr(a.index_name,1,30), a.uniqueness /

• eof ---------------------------

--
See http://osi.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st  

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Tue Jan 18 2000 - 15:40:42 CST