|
|
|
|
|
Index_ColOrder( table_name, index_id, N [, owner_id] )
Returns the sort order of the Nth column in the specified index.
Example
select Index_Col( 'T_ProdBillOfMat', IndId, 1)
, Index_ColOrder( 'T_ProdBillOfMat', IndId, 1)
from sysindexes
where Id = Object_Id('T_ProdBillOfMat')
and name = 'PK_T_ProdBillOfMat'
Difference
In MS SQL Server, the function Index_ColOrder does not exist. However, information about the columns of an index are stored in a readable format in the database. The following query yields the same result as the ASE query in the example.
Select Index_Col( 'T_ProdBillOfMat', IDX.index_id, 1)
, case IDXCOL.is_descending_key
when 0 then 'ASC'
else 'DESC'
end
from sys.indexes IDX inner join sys.index_columns IDXCOL
on (IDXCOL.object_id = IDX.object_id and IDXCOL.index_id = IDX.index_id)
where IDX.object_id = Object_Id('T_ProdBillOfMat')
and IDX.name = 'PK_T_ProdBillOfMat'
and IDXCOL.index_column_id = 1