sp_indexes

Transact-SQL Reference

Transact-SQL Reference

sp_indexes

Returns index information for the specified remote table.

Syntax

sp_indexes [ @table_server = ] 'table_server'
    [ , [@table_name = ] 'table_name' ]
    [ , [ @table_schema = ] 'table_schema' ]
    [ , [ @table_catalog = ] 'table_db' ]
    [ , [ @index_name = ] 'index_name' ]
    [ , [ @is_unique = ] 'is_unique' ]

Arguments

[@table_server =] 'table_server'

Is the name of a linked server running Microsoft® SQL Server™ for which table information is being requested. table_server is sysname, with no default.

[@table_name =] 'table_name'

Is the name of the remote table for which to provide index information. table_name is sysname, with a default of NULL. If NULL, all tables in the specified database are returned.

[@table_schema =] 'table_schema'

Specifies the table schema. In the Microsoft SQL Server environment, this corresponds to the table owner. table_schema is sysname, with a default of NULL.

[@table_catalog =] 'table_db'

Is the name of the database in which table_name resides. table_db is sysname, with a default of NULL. If NULL, table_db defaults to master.

[@index_name =] 'index_name'

Is the name of the index for which information is being requested. index is sysname, with a default of NULL.

[@is_unique =] 'is_unique'

Is the type of index for which to return information. is_unique is bit, with a default of NULL, and can be one of these values.

Value Description
1 Returns information about unique indexes.
0 Returns information about indexes that are not unique.
NULL Returns information about all indexes.

Result Sets
Column name Data type Description
TABLE_CAT sysname Name of the database in which the specified table resides.
TABLE_SCHEM sysname Schema for the table.
TABLE_NAME sysname Name of the remote table.
NON_UNIQUE smallint Whether the index is unique or not unique:

0 = Unique
1 = Not unique

INDEX_QUALIFER sysname Name of the index owner. 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 Name of the index.
TYPE smallint Type of index:

0 = Statistics for a table
1 = Clustered
2 = Hashed
3 = Other

ORDINAL_POSITION int Ordinal position of the column in the table. The first column in the table is 1. This column always returns a value.
COLUMN_NAME sysname Is the corresponding name of the column for each column of the TABLE_NAME returned.
ASC_OR_DESC varchar Is the order used in collation:

A = Ascending
D = Descending
NULL = Not applicable

SQL Server always returns A.

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 nvarchar(4000) SQL Server does not return a value.

Permissions

Execute permissions default to the public role.

Examples

This example returns all index information from the Employees table of the Northwind database on the LONDON1 database server.

EXEC sp_indexes @table_server = 'LONDON1', 
   @table_name = 'Employees', 
   @table_catalog = 'Northwind', 
   @is_unique = 0

See Also

sp_catalogs

sp_column_privileges

sp_foreignkeys

sp_indexes

sp_linkedservers

sp_tables_ex

sp_table_privileges

System Stored Procedures