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