sp_indexes (Transact-SQL)
Applies to:
SQL Server
Returns index information for the specified remote table.
Transact-SQL syntax conventions
Syntax
sp_indexes
[ @table_server = ] N'table_server'
[ , [ @table_name = ] N'table_name' ]
[ , [ @table_schema = ] N'table_schema' ]
[ , [ @table_catalog = ] N'table_catalog' ]
[ , [ @index_name = ] N'index_name' ]
[ , [ @is_unique = ] is_unique ]
[ ; ]
Arguments
[ @table_server = ] N'table_server'
The name of a linked server running SQL Server for which table information is being requested. @table_server is sysname, with no default.
[ @table_name = ] N'table_name'
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 = ] N'table_schema'
Specifies the table schema. @table_schema is sysname, with a default of NULL
. In the SQL Server environment, this value corresponds to the table owner.
[ @table_catalog = ] N'table_catalog'
The name of the database in which @table_name resides. @table_catalog is sysname, with a default of NULL
. If NULL
, @table_catalog defaults to master
.
[ @index_name = ] N'index_name'
The name of the index for which information is being requested. @index_name is sysname, with a default of NULL
.
[ @is_unique = ] is_unique
The type of index for which to return information. @is_unique is bit, and can be one of the following values.
Value | Description |
---|---|
1 |
Returns information about unique indexes. |
0 |
Returns information about indexes that aren't unique. |
NULL (default) |
Returns information about all indexes. |
Result set
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 = Unique1 = Not unique |
INDEX_QUALIFER |
sysname | Name of the index owner. Some database management system (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 table1 = Clustered2 = Hashed3 = 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 | The corresponding name of the column for each column of the TABLE_NAME returned. |
ASC_OR_DESC |
varchar | The order used in collation:A = AscendingD = DescendingNULL = Not applicableSQL Server always returns A . |
CARDINALITY |
int | The number of rows in the table or unique values in the index. |
PAGES |
int | The number of pages to store the index or table. |
FILTER_CONDITION |
nvarchar(4000) | SQL Server doesn't return a value. |
Permissions
Requires SELECT
permission on the schema.
Examples
The following example returns all index information from the Employees
table of the AdventureWorks2022
database on the Seattle1
linked server.
EXEC sp_indexes @table_server = 'Seattle1',
@table_name = 'Employee',
@table_schema = 'HumanResources',
@table_catalog = 'AdventureWorks2022';
Related content
Feedback
https://aka.ms/ContentUserFeedback.
Binnenkort: Gedurende 2024 worden GitHub Issues uitgefaseerd als het feedbackmechanisme voor inhoud. Dit wordt vervangen door een nieuw feedbacksysteem. Ga voor meer informatie naar:Feedback verzenden en bekijken voor