Using system tables to identify the columns of a table index in Sybase

sybase

Is there a means of identifying the columns of table index using systematically populated system tables such as: syscolumns, sysindexes, sysobjects, etc.

I have tried to use syskeys unfortunately this table is only populated by sp_primarykey.

So far I have the following query:

SELECT 
    i.name,
    o.name
FROM 
    sysobjects o
    INNER JOIN sysindexes i on i.id = o.id
WHERE
    o.type = 'U'
    and o.name = 'XXXXX'
      and i.indid > 0
      and i.status & 2 = 2

Best Answer

I came up with the following solution - thanks to the comment by Phil:

SELECT
     distinct i.name as index_name, index_col(o.name, i.indid, c.colid) as name
FROM 
    sysobjects o 
    INNER JOIN sysindexes i on i.id = o.id
    INNER JOIN syscolumns c on c.id = o.id
WHERE
    o.type = 'U'
    and o.name = 'XXXXXX'
      and i.indid > 0
      and i.status & 2 = 2 -- Gives only system indexes. Skip it, and get all
      and index_col(o.name, i.indid, c.colid) <> null
    order by 1, 2
Related Question