Chapter 4: Executing Remote Procedure Calls

sp_statistics

Description

Returns a list of indexes on a single table.

Syntax

server_name... 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.

table_qualifier: is ignored. Null should be specified.

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 non-unique indexes.

Examples

Example 1

DCSERVER...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

Table 4-4 describes the results set:

**Table 4-4: Results set for sp_statistics**
Column	Datatype	Description
table_qualifier	varchar(32)	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.

Permissions

Any user can execute sp_statistics.

View this book as PDF