| title | sp_indexes (Transact-SQL) | Microsoft Docs | ||
|---|---|---|---|
| ms.custom | |||
| ms.date | 03/14/2017 | ||
| ms.prod | sql | ||
| ms.prod_service | database-engine | ||
| ms.reviewer | |||
| ms.technology | system-objects | ||
| ms.topic | language-reference | ||
| f1_keywords |
|
||
| dev_langs |
|
||
| helpviewer_keywords |
|
||
| ms.assetid | 25469e72-9d95-463f-912a-193471c8f5e2 | ||
| author | stevestein | ||
| ms.author | sstein |
[!INCLUDEtsql-appliesto-ss2008-xxxx-xxxx-xxx-md]
Returns index information for the specified remote table.
Transact-SQL Syntax Conventions
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' ]
[ @table_server= ] 'table_server'
Is the name of a linked server running [!INCLUDEssNoVersion] 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 [!INCLUDEssNoVersion] 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 the following values.
| Value | Description |
|---|---|
| 1 | Returns information about unique indexes. |
| 0 | Returns information about indexes that are not unique. |
| NULL | Returns information about all indexes. |
| 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 for users other than the table owner to create indexes. In [!INCLUDEssNoVersion], 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 index. The first column in the index 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 [!INCLUDEssNoVersion] 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) | [!INCLUDEssNoVersion] does not return a value. |
Requires SELECT permission on the schema.
The following example returns all index information from the Employees table of the AdventureWorks2012 database on the Seattle1 linked server.
EXEC sp_indexes @table_server = 'Seattle1',
@table_name = 'Employee',
@table_schema = 'HumanResources',
@table_catalog = 'AdventureWorks2012';
Distributed Queries Stored Procedures (Transact-SQL)
sp_catalogs (Transact-SQL)
sp_column_privileges (Transact-SQL)
sp_foreignkeys (Transact-SQL)
sp_linkedservers (Transact-SQL)
sp_tables_ex (Transact-SQL)
sp_table_privileges (Transact-SQL)
System Stored Procedures (Transact-SQL)