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

Transact-SQL

rusag2


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.




Top

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

hunchback


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.indexes as si

inner join

sys.index_columns as sic

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

and si.index_id = sic.index_id

where

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

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

order by

sic.[object_id],

sic.index_id

go

AMB



Top

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

rusag2

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

Any cute tricks for the equivalent query on SS 2K






Top

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

hunchback

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

sysindexes as si

inner join

sysindexkeys as sic

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

and si.indid = sic.indid

where

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

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

order by

sic.[id],

sic.indid

go

AMB


Top