For a given table, I want to know all the columns that are included in
an index. I have looked on the web and come up with this, which seems
to work, but just wanted some verification. Are there any reasons why
I should be using the metadata functions like OBJECT_NAME?

Thanks
Bruce

SELECT
DISTINCT c.name
from sysusers u,
sysobjects o,
syscolumns c,
sysindexes i,
sysindexkeys k
WHERE o.uid = u.uid
AND u.name = user
AND o.name = 'ing_customer'
AND o.id = i.id
AND i.indid = k.indid
AND OBJECTPROPERTY( i.id, 'IsMSShipped' ) = 0
AND 1 NOT IN ( INDEXPROPERTY( i.id , i.name , 'IsStatistics' ) ,
INDEXPROPERTY( i.id , i.name , 'IsAutoStatistics' ) ,
INDEXPROPERTY( i.id , i.name , 'IsHypothetical' ) )
AND i.indid BETWEEN 1 And 250
AND k.id = o.id
and k.colid = c.colid
and c.id = o.id

Bruce (sandell@pacbell.net) writes:
[quoted text, click to view]

object_name() is just a quick-step to sysobjects.name, which is a
documented column, so which one you use, is no big deal.

On the other hand, the information returned by objectproperty() and
indexproperty() is hidden in undocuemented columns, so in this case,
you should definitely use the metadata functions.


--
Erland Sommarskog, SQL Server MVP, sommar@algonet.se

Books Online for SQL Server SP3 at