INDEXKEY_PROPERTY (Transact-SQL)

Returns information about the index key. Returns NULL for XML indexes.

Important

This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature. Instead, use sys.index_columns (Transact-SQL).

Transact-SQL Syntax Conventions

Syntax

INDEXKEY_PROPERTY ( object_ID ,index_ID ,key_ID ,property )

Arguments

• object_ID
  Is the object identification number of the table or indexed view. object_ID is int.

• index_ID
  Is the index identification number. index_ID is int.

• key_ID
  Is the index key column position. key_ID is int.

• property
  Is the name of the property for which information will be returned. property is a character string and can be one of the following values.

  Value	Description
  ColumnId	Column ID at the key_ID position of the index.
  IsDescending	Order in which the index column is stored. 1 = Descending 0 = Ascending

Return Types

int

Exceptions

Returns NULL on error or if a caller does not have permission to view the object.

In SQL Server 2005, a user can only view the metadata of securables that the user owns or on which the user has been granted permission. This means that metadata-emitting, built-in functions such as INDEXKEY_PROPERTY may return NULL if the user does not have any permission on the object. For more information, see Metadata Visibility Configuration and Troubleshooting Metadata Visibility.

Examples

In the following example, both properties are returned for index ID 1, key column 1 in the Production.Location table.

USE AdventureWorks;
GO
SELECT 
    INDEXKEY_PROPERTY(OBJECT_ID('Production.Location', 'U'),
        1,1,'ColumnId') AS [Column ID],
    INDEXKEY_PROPERTY(OBJECT_ID('Production.Location', 'U'),
        1,1,'IsDescending') AS [Asc or Desc order];

Here is the result set:

Column ID   Asc or Desc order 
----------- ----------------- 
1           0

(1 row(s) affected)

See Also

Reference

INDEX_COL (Transact-SQL)
INDEXPROPERTY (Transact-SQL)
sys.objects (Transact-SQL)
sys.indexes (Transact-SQL)
sys.index_columns (Transact-SQL)

Help and Information

Getting SQL Server 2005 Assistance

Change History

Release	History
17 July 2006	New content: Added the Exceptions section.