Returns information about the index key. Returns NULL for XML indexes.
INDEXKEY_PROPERTY ( object_ID ,index_ID ,key_ID ,property )
Is the object identification number of the table or indexed view. object_ID is int.
Is the index identification number. index_ID is int.
Is the index key column position. key_ID is int.
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.
|ColumnId||Column ID at the key_ID position of the index.|
|IsDescending||Order in which the index column is stored.|
1 = Descending 0 = Ascending
Returns NULL on error or if a caller does not have permission to view the object.
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.
In the following example, both properties are returned for index ID
1, key column
1 in the
USE AdventureWorks2012; 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)