Getting the name of a column used by an index using its id

Hello all

Im trying to get the name of a column used in an index using only its id found in the sys.indexes.

Is there a function like db_name or object_name for the column?

It would be usefull in the following code:

[Sorry if the comments are in french but they are mostly meaningless]

Code Snippet

DECLARE @.sql nvarchar(500) -- requete sql en cours

DECLARE @.Bdname nchar(50) -- bd rendue

DECLARE @.bdid int

-

-- Boucle sur la liste des bases de donnes --

-

DECLARE listebd INSENSITIVE CURSOR FOR

Select name,dbid from master.dbo.sysdatabases

OPEN listebd

FETCH listebd INTO @.bdname,@.bdid

WHILE @.@.FETCH_STATUS = 0

BEGIN

-- Pour chaque base de donne, vrifie les index --

-- pour vrifier lesquels sont valide --

SET @.sql = '

USE ' + @.Bdname + '

SELECT [Column name],object_name(i.object_id) as [Objet],

i.name as [index_name],i.index_id

FROM sys.indexes i, sys.objects o

where objectproperty(o.object_id,''isUserTable'')=1

and i.index_id NOT IN (SELECT s.index_id

FROM sys.dm_db_index_usage_stats s

where s.object_id=i.object_id and

i.indeX_id=s.index_id and

database_id = ' + convert(nvarchar(20),@.bdid) + ')

and o.object_id = i.object_id AND object_name(i.object_id) NOT LIKE ''sys%'''

EXEC sp_executesql @.sql

print @.bdid

-- Passe a la base de donne suivante ou termine --

FETCH listebd INTO @.bdname,@.bdid

END -- Fin du curseur de base de donnes

CLOSE listebd

DEALLOCATE listebd

Use COL_NAME()

Syntax:

COL_NAME(object_id, column_index)

SELECT COL_NAME(OBJECT_ID('SYSOBJECTS'),1)

|||

Code Snippet

select idx.name, icols.index_column_id, cols.name as [column name]

from sys.indexes idx

inner join sys.index_columns icols

on idx.index_id = icols.index_id

and idx.object_id = icols.object_id

inner join sys.columns cols

on icols.column_id = cols.column_id

and idx.object_id = cols.object_id

where idx.object_id = object_id('Person.Address')

sql