1

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
1

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
      and index_col(o.name, i.indid, c.colid) <> null
    order by i.name
0

For the sake of someone's sanity, munyengm has this correct but you may need to pass in the object owner as well if it isn't dbo or your current user.

**SELECT
     distinct i.name as index_name, index_col(o.name, i.indid, c.colid, o.uid) 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
      and index_col(o.name, i.indid, c.colid, o.uid) <> null
    order by i.name**
-1

In Sybase version SAP IQ/16, you can get list of indexes with following (table name my_table is case-sensitive):

select *
from sys.sysindexes
where tname = 'my_table';
  • The question refers to obtaining a list of columns in each index. This only lists the actual indexes themselves, without the column names. – Max Vernon Mar 8 '17 at 19:13
  • @max-vernon, In the version I use (SAP IQ/16.0.102.6.1386/20147/P/sp10.15) the sys.sysindexes table includes column names in the column: colnames. The table has following columns: icreator iname fname creator tname indextype colnames interval level_num – Orhan Celik Mar 9 '17 at 16:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.