sysindexes table?

[ColinM]

Hi,
Does anyone know how to use the keys column in sysindexes?
According to BOL it is:
List of the column IDs of the columns that make up the index key.
But I don't know how to make sense of this strange varbinary(816) field.
I would like the column names that the index uses displayed.
Any ideas?

 

[TomSark]

If you want the columns that comprise the key values for the index, join the colid column from the sysindexes table to the colid column in the syscolumns table, the name column in the syscolumns table will tell you the column name... Tom Davis
tdavis@sark.com

http://www.sarkdallas.com

 

[TomSark]

So... here's an example:

SELECT sc.name
FROM syscolumns sc
INNER JOIN sysindexes si ON si.id = sc.colid
WHERE si.name = 'MY INDEX NAME'

I typo'd in the last message... note it's the 'id' column in sysindexes... Tom Davis
tdavis@sark.com

http://www.sarkdallas.com

 

[ColinM]

Didn't quite work!
I think you were closer the first time, joining the ID's like this:

SELECT sc.name as ColumnName, si.name as IndexName
FROM syscolumns sc
INNER JOIN sysindexes si ON si.id = sc.id
where si.name = 'INDEX_NAME'

This gives me ALL the columns in the table which the index is on, but I don't know how to perform a join so that only the columns in the index are shown.
Kinda like

sc.colid = si.colid

except si.colid doesn't exist!

 

[ColinM]

Got it!
This query gives indexes by table

SELECT o.name as

, i.name AS [Index], c.name AS [Column]
FROM sysindexkeys k INNER JOIN
syscolumns c ON k.id = c.id AND
k.colid = c.colid INNER JOIN
sysindexes i ON k.id = i.id AND
k.indid = i.indid INNER JOIN
sysobjects o ON c.id = o.id
WHERE o.type = 'U'
ORDER BY o.name, i.name, c.name