how to find if index key is DESC from sy tables?

• agoyal

  SSC Rookie

  Points: 46

  More actions

  April 29, 2003 at 6:00 pm

  #158882

  There is a index: CustomerInfo_1

  with keys: customerId, EnteryDate DESC

  I could not find where the order of index key (i.e. whether the key is ascending or descending) is stored?

  I tried system tables such as sysindexes and sysindexkeys tables. But could not find it.

  Any help in this regard will be truly appreciated.

  Thank you.

  Regards,

  Anuj Goyal

• K. Brian Kelley

  SSC Guru

  Points: 114704

  More actions

  April 29, 2003 at 6:41 pm

  #455750

  You're going to have to use the indexkey_property() system function in order to get this information. For instance, the logic from sp_helpindex that determines whether or not an index is ascending or descending:

  
  
    -- First we'll figure out what the keys are.  
  
    declare @i int, @thiskey nvarchar(131) -- 128+3  
  
    
  
    select @keys = index_col(@objname, @indid, 1), @i = 2  
  
    if (indexkey_property(@objid, @indid, 1, 'isdescending') = 1)  
  
     select @keys = @keys  + '(-)'  
  
    
  
    select @thiskey = index_col(@objname, @indid, @i)  
  
    if ((@thiskey is not null) 
  
      and (indexkey_property(@objid, @indid, @i, 'isdescending') = 1))  
  
        select @thiskey = @thiskey + '(-)'  
  
    
  
    while (@thiskey is not null )  
  
    begin  
  
     select @keys = @keys + ', ' + @thiskey, @i = @i + 1  
  
     select @thiskey = index_col(@objname, @indid, @i)  
  
     if ((@thiskey is not null) 
  
       and (indexkey_property(@objid, @indid, @i, 'isdescending') = 1))  
  
         select @thiskey = @thiskey + '(-)'  
  
    end  
  
  

  You can get this information by executing EXEC sp_helptext sp_helpindex in the master database.

  K. Brian Kelley

  http://www.truthsolutions.com/

  Author: Start to Finish Guide to SQL Server Performance Monitoring

  http://www.netimpress.com/shop/product.asp?ProductID=NI-SQL1

  Edited by - bkelley on 04/29/2003 6:42:39 PM

  K. Brian Kelley
  @kbriankelley