Note
Access to this page requires authorization. You can try signing in or changing directories.
Access to this page requires authorization. You can try changing directories.
Returns index information for the specified remote table.
.gif "Topic link icon")
Transact-SQL Syntax Conventions
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 SQL Server 2005 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 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 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.
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 for 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 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 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
Requires SELECT permission on the schema.
Examples
The following example returns all index information from the Employees table of the AdventureWorks database on the Seattle1 linked server.
EXEC sp_indexes @table_server = 'Seattle1',
@table_name = 'Employee',
@table_schema = 'HumanResources',
@table_catalog = 'AdventureWorks'
See Also
Reference
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)