How to get columnname of an index from sysindexes

[ptmcs]

Hi,

I have a bit of SQL whereby I have a list of tables,
from that I can get the 'id' of each table from sysobjects.
Using the 'id' and 'indid' of 1 - I can derive the name
of the primary key -
however, I am not sure then which table to interrogate
to get the name of the actual column the primary key
refers too...
Any ideas?

 

[JamesLean]

Have a look at the information_schema.key_column_usage view.

--James

 

[vongrunt]

While information_schema views are without doubt useful, for hardcode digging I found them insufficient. key_column_usage view doesn't provide information about constraint type; assuming that constraint names beginning with "PK_" represent primary keys is... well, assumption. Btw. is there any I-schema view for vanilla (non-unique) indexes?

Without sys* things, check sproc named sp_pkeys. Otherwise: proceed .

sysindexes.indid=1 indicates clustered index, NOT primary key. In 99% of cases this is basically the same. Assumption again. To get 100% check status bitmask column:

case when (sysindexes.status & 0x800) = 0x800 then 1 else 0 end as isPrimaryKey

For each (id, indid) index in sysindexes, columns are defined in sysindexkeys. Use (id, colid) to join on syscolumns and get column names. keyno is ordinal column position within index key (may be >1 for composite indexes).

------
Math problems? Call 0800-[(10x)(13i)^2]-[sin(xy)/2.362x]