Returns a list of all indexes on a specified table.
sp_statistics [@table_name =] 'table_name'
[,[@table_owner =] 'owner']
[,[@table_qualifier =] 'qualifier']
[,[@index_name =] 'index_name']
[,[@is_unique =] 'is_unique']
[,[@accuracy =] 'accuracy']
In Microsoft® SQL Server™, if the current user owns a table with the specified name, the indexes of that table are returned. If owner is not specified and the current user does not own a table with the specified name, this procedure looks for a table with the specified name owned by the database owner. If one exists, the indexes of that table are returned.
Column name | Data type | Description |
---|---|---|
TABLE_QUALIFIER | sysname | Table qualifier name. This field can be NULL. |
TABLE_OWNER | sysname | Table owner name. This field always returns a value. |
TABLE_NAME | sysname | Table name. This field always returns a value. |
NON_UNIQUE | smallint | NOT NULL. 0 = Unique 1 = Not unique |
INDEX_QUALIFIER | sysname | Index owner name. Some DBMS products allow users other than the table owner to create indexes. In SQL Server, this column is always the same as TABLE_NAME. |
INDEX_NAME | sysname | Is the name of the index. This field always returns a value. |
TYPE | smallint | This field always returns a value. SQL Server returns 0, 1, 2, or 3: 0 = Statistics for a table 1 = Clustered 2 = Hashed 3 = Other |
SEQ_IN_INDEX | smallint | Position of the column within the index. |
COLUMN_NAME | sysname | Column name for each column of the TABLE_NAME returned. This field always returns a value. |
COLLATION | char(1) | Order used in collation. SQL Server always returns A. Can be: A = Ascending D = Descending NULL = 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. |
FILTER_CONDITION | varchar(128) | SQL Server does not return a value. |
None
The indexes in the result 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.
sp_statistics is equivalent to SQLStatistics in ODBC. The results returned are ordered by NON_UNIQUE, TYPE, INDEX_QUALIFIER, INDEX_NAME, and SEQ_IN_INDEX.
Execute permissions default to the public role.