sp_helpgroup  sp_helpjava

Chapter 1: System Procedures

sp_helpindex

Description

Reports information about the indexes created on a table. Reports information on computed column indexes and function-based indexes.

Syntax

sp_helpindex objname

Parameters

objname

is the name of a table in the current database.

Examples

Example 1

Displays the types of indexes on the sysobjects table:

sp_helpindex sysobjects
index_name index_keys index_description index_max_rows_per_page 
       index_fillfactor index_reservepagegap index_created
       index_local

sysobjects    id        clustered, unique                    0
                   0                  0        Apr 12 2005  2:38PM
        Global Index
ncsysobjects  name, uid nonclustered, unique
                   0                  0        Apr 12 2005  2:38PM
        Global Index

(2 rows affected)
index_pt_name          index_ptn_seg
---------------------- --------------
sysobjects_1            system
ncsysobjects_1           system

Example 2

Displays information about the index on the titles table in the pubs2 database. The titles table is partitioned, but the index titleind is not. titleind is a nonclustered (single-partitioned), global index.

sp_helpindex titles
index_name index_keys index_description index_max_rows_per_page 
       index_fillfactor index_reservepagegap index_created
       index_local

titleind    title    nonclustered                            0
          Global Index

(1 row affected)
index_pt_name          index_ptn_seg
---------------------- --------------
titleind_1232004389     default

Example 3

Displays index information about the mysalesdetail table. mysalesdetail is partitioned by hash on the ord_num column. A clustered, local index, with three partitions, has also been created on ord_num.

sp_helpindex mysalesdetail
index_name index_keys index_description index_max_rows_per_page 
       index_fillfactor index_reservepagegap index_created
       index_local

clust_idx    ord_num        clustered                             0
                             0             0        Apr 12 2005  2:38PM
        Local Index
(1 row affected)
index_pt_name          index_ptn_seg
---------------------- --------------
clust_idx_1344004788   default
clust_idx_1360004845   default
clust_idx_1376004902   default

Example 4

Displays a function-based index:

create index sum_sales on mytitles (price * total_sales)
sp_helpindex mytitles
Object has the following indexes








index_name index_keys index_description index_max_rows_per_page
    index_fillfactor index_reservepagegap index_created
    index_local 
---------- ---------- ----------------- -----------------------
    ---------------- -------------------- --------------------------
    ------------
sum_sales   sybfi2_1  nonclustered                            0
    0                                   0   Oct 12 2005  3:34PM
    Global Index

(1 row affected)
index_ptn_name       index_ptn_seg
-------------------- -------------
sum_sales_1724867646 default 

(1 row affected)

Object has the following functional index keys

Internal_Index_Key_Name
-----------------------
sybfi2_1 

(1 row affected)

Expression
-------------------
price * total_sales

(return status = 0)

Usage

Permissions

Any user can execute sp_helpindex.

Auditing

Values in event and extrainfo columns from the sysaudits table are:

Event

Audit option

Command or access audited

Information in extrainfo

38

exec_procedure

Execution of a procedure

  • Roles – Current active roles

  • Keywords or options – NULL

  • Previous value – NULL

  • Current value – NULL

  • Other information – All input parameters

  • Proxy information – Original login name, if set proxy in effect

See also

Commands create index, drop index, update statistics

System procedures sp_help, sp_helpkey, sp_helpartition





Copyright © 2005. Sybase Inc. All rights reserved. sp_helpjava

View this book as PDF