sp_statistics

Description

Returns a list of indexes on a single table.

Syntax

sp_statistics table_name [, table_owner] 
	[, table_qualifier] [, index_name] [, is_unique]

Parameters

table_name

is the name of the table. The use of wildcard character pattern matching is not supported.

table_owner

is the owner of the table. The use of wildcard character pattern matching is not supported. If table_owner is not specified, sp_statistics looks for a table owned by the current user and then for a table owned by the Database Owner.

table_qualifier

is the name of the database. This can be either the current database or NULL.

index_name

is the index name. The use of wildcard character pattern matching is not supported.

is_unique

is Y to return only unique indexes; otherwise, is N to return both unique and nonunique indexes.

Examples

Example 1

sp_statistics publishers

table_qualifier                  table_owner
    table_name                       non_unique
    index_qualifier                  index_name
    type   seq_in_index column_name                     collation
    cardinality pages       
-------------------------------- --------------------------------
   -------------------------------- ---------- 
   -------------------------------- ---------------------------
   ------ ------------ -------------------------------- -------- 
   ----------- ----------- 
pubs2                            dbo
    publishers                       NULL
    NULL                             NULL
        0         NULL NULL                             NULL
             3           1 
pubs2                            dbo
    publishers                                0
    publishers                       pubind
         1            1 pub_id                           A
             3           1 

Usage

• The results set for sp_statistics is:

  Column	Datatype	Description
  table_qualifier	varchar(32)	The database name. This field can be NULL.
  table_owner	varchar(32)
  table_name	varchar(32)	NOT NULL.
  non_unique	smallint	NOT NULL. The value 0 means unique, and 1 means not unique.
  index_qualifier	varchar(32)
  index_name	varchar(32)
  type	smallint	NOT NULL. The value 0 means clustered, 2 means hashed, and 3 means other.
  seq_in_index	smallint	NOT NULL.
  column_name	varchar(32)	NOT NULL.
  collation	char(1)	The value A means ascending; D means descending; and NULL means not applicable.
  cardinality	int	Number of rows in the table or unique values in the index.
  pages	int	Number of pages to store the index or table.

• The indexes in the results set appear in ascending order, ordered by the non-unique, type, index_name, and seq_in_index columns.

• The index type hashed accepts exact match or range searches, but searches involving pattern matching do not use the index.

Permissions

Any user can execute sp_statistics.

Copyright © 2005. Sybase Inc. All rights reserved.

View this book as PDF