sp_statistics Catalog Stored Procedures

Returns a list of all indexes on a specified table.

Syntax

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

where

table_name: Specifies the table used to return catalog information. The table_name can be a variable character length name with a maximum of 32 characters. Wildcard pattern matching is not supported.
table_owner: Specifies the table owner of the table used to return catalog information. The table_owner can be a variable character name with a maximum 32 characters. Wildcard pattern matching is not supported. If the table_owner is not specified, the default table visibility rules of the underlying DBMS apply.
In Microsoft SQL Server, if the current user owns a table with the specified name, that table's columns are returned. If the table_owner is not specified and the current user does not own a table with the specified table_name, this procedure will look for a table with the specified table_name owned by the database owner. If one exists, that table's columns are returned.
table_qualifier: Is the name of the table qualifier. Various DBMS products support three-part naming for tables (qualifier.owner.name). In Microsoft SQL Server, this column represents the database name. In some products, it represents the server name of the table's database environment.
index_name (varchar(32)): Is the index name. Wildcard pattern matching is not supported.
is_unique (char(1)): Identifies indexes to be returned. Enter Y if only unique indexes are to be returned.

Remarks

The indexes in the results set appear in ascending order by the columns NON_UNIQUE, TYPE, INDEX_NAME, and SEQ_IN_INDEX.

The index type clustered refers to an index in which table data is stored in the order of the index. This corresponds to SQL Server clustered indexes.

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

The sp_statistics stored procedure is equivalent to SQLStatistics in ODBC. The results returned are ordered by NON_UNIQUE, TYPE, INDEX_QUALIFIER, INDEX_NAME, and SEQ_IN_INDEX.

This is the results set:



Column	Datatype	Description

TABLE_QUALIFIER	varchar(32)	Is the name of the table qualifier. Various DBMS products support three-part naming for tables (qualifier.owner.name). In Microsoft SQL Server, this column represents the database name. In some products, it represents the server name of the table's database environment. This field can be NULL.
TABLE_OWNER	varchar(32)	Is the name of the table owner. Various DBMS products support three-part naming for tables (qualifier.owner.name). In Microsoft SQL Server, this column represents the name of the database user who created the table. This field will always return a value.
TABLE_NAME	varchar(32)	Is the name of the table. Various DBMS products support three-part naming for tables (qualifier.owner.name). In Microsoft SQL Server, this column represents the table name as listed in the sysobjects table. This field will always return a value.
NON_UNIQUE	smallint	NOT NULL. 0 means unique; 1 means non unique.
INDEX_QUALIFIER	varchar(32)	Is the name of the index owner. Some DBMS products allow users other than the table owner to create indexes. In Microsoft SQL Server, this column will always be the same as TABLE_OWNER.
INDEX_NAME	varchar(32)	Is the name of the index. This field will always return a value.
TYPE	smallint	This field will always return a value. SQL Server 6.0 returns 0, 1, or 3. Can be: 0 Statistics for a table 1 Clustered 2 Hashed 3 Other
SEQ_IN_INDEX	smallint	Is the position of the column within the index.
COLUMN_NAME	varchar(32)	Is the name of the column, for each column of the TABLE_NAME returned. In Microsoft SQL Server, this column represents the column name as listed in the syscolumns table. This field will always return a value.
COLLATION	char(1)	Is the order used in collation. SQL Server 6.0 always returns A. Can be: A Ascending D Descending NULL Not applicable
CARDINALITY	int	Is the number of rows in the table or unique values in the index.
PAGES	int	Is the number of pages to store the index or table.
FILTER_CONDITION	varchar(128)	SQL Server 6.0 does not return a value.