sp_statistics
Returns a list of all indexes and statistics on a specified table or indexed view.
Syntax
sp_statistics [@table_name =] 'table_name'
[,[@table_owner =] 'owner']
[,[@table_qualifier =] 'qualifier']
[,[@index_name =] 'index_name']
[,[@is_unique =] 'is_unique']
[,[@accuracy =] 'accuracy']
Arguments
[@table_name =] 'name'
Specifies the table used to return catalog information. table_name is sysname, with no default. Wildcard pattern matching is not supported.
[@table_owner =] 'owner'
Is the name of the table owner of the table used to return catalog information. table_owner is sysname, with a default of NULL. Wildcard pattern matching is not supported. If 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, 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.
[@table_qualifier =] 'qualifier'
Is the name of the table qualifier. qualifier is sysname, with a default of NULL. Various DBMS products support three-part naming for tables (qualifier.owner.name). In SQL Server, this parameter represents the database name. In some products, it represents the server name of the table's database environment.
[@index_name =] 'index_name'
Is the index name. index_name is sysname, with a default of %.
[@is_unique =] 'is_unique'
Is whether only unique indexes (if Y) are to be returned. is_unique is char(1), with a default of N.
[@accuracy =] 'accuracy'
Is the level of cardinality and page accuracy for statistics. accuracy is char(1), with a default of Q. Specify E to ensure that statistics are updated so that cardinality and pages are accurate.
Result Sets
| 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. |
Return Code Values
None
Remarks
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.
Permissions
Execute permissions default to the public role.