How to select index column?

[Rohullah]

Hello

I want to select all those column from a table on which i have create index.


how to do it i am new with sql server.

thank u in advance.

[viktorka]

Why, don't you know the name of the columns? Where would you like to pass these names?

[Jerry Winston]

All the information about the objects you create like tables, indexes, and columns can be accessed through metadata views. I dont know if you're planning to get SQL certified but even if you're not interested in becoming a db guru, it's worth learning your system views when working with MS SQL server. Here's a solution for you:

SELECT s.name as [Schema],t.name as [Table],c.name [Column],i.name as [Index] FROM sys.index_columns ic
INNER JOIN
sys.tables t
on
t.object_id=ic.object_id
INNER JOIN
sys.columns c
ON
c.column_id = ic.column_id
AND
c.object_id=t.object_id
INNER JOIN
sys.schemas s
ON
t.schema_id=s.schema_id
INNER JOIN
sys.indexes i
on
i.index_id=ic.index_id
AND
i.object_id=t.object_id
where t.type='U'