Determine if a column is indexed (and the index name)

Is there a slick way to determine if all the indexes (if any) for a particular field?

let's say:

tbPeople

ID (bigint)

FirstName (varchar(50))

LastName (varchar(50))

I need to determine if [FirstName] in indexed anywhere, and if so, what are the name(s) of the indexes.

thanks ahead of time.

Which version of SS are you using?

To select all indexes where the column is part of the key, in 2005, you can use:

select

object_name(si.[object_id])as table_name,

si.name as index_name,

index_col(object_name(sic.[object_id]), sic.index_id, sic.index_column_id)as column_name,

sic.key_ordinal,

sic.is_descending_key,

sic.is_included_column

from

sys.indexesas si

innerjoin

sys.index_columnsas sic

on si.[object_id] = sic.[object_id]

and si.index_id = sic.index_id

where

si.object_id=object_id('dbo.Employees')

andcolumnproperty(object_id('dbo.Employees'),'LastName','ColumnId')= sic.column_id

orderby

sic.[object_id],

sic.index_id

go

AMB

|||

Brilliant. Thanks. Was not aware of sys.index_columns.

Any cute tricks for the equivalent query on SS 2K?

|||

Try:

select

object_name(si.[id])as table_name,

si.name as index_name,

col_name(sic.[id], sic.colid)as column_name,

sic.keyno

from

sysindexesas si

innerjoin

sysindexkeysas sic

on si.[id] = sic.[id]

and si.indid = sic.indid

where

si.[id] =object_id('dbo.Employees')

andcol_name(sic.[id], sic.colid)='LastName'

orderby

sic.[id],

sic.indid

go

AMB