indexes list on a particular column

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

indexes list on a particular column

Author

Topic

js.reddy
Yak Posting Veteran

80 Posts

Posted - 2009-07-13 : 09:22:28

Hi Folks,

I have a column named custid. I used this column in so many tables.
And so many indexes also there on this columns in different tables.
Now i want to know the all indexes list on this column

Regards
JS.Reddy

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2009-07-13 : 10:09:57

select * from
INFORMATION_SCHEMA.KEY_COLUMN_USAGE

where column_name = 'cust_id'

Jim

js.reddy
Yak Posting Veteran

80 Posts

Posted - 2009-07-13 : 10:35:04

sorry, it will not work. it will give constraint names only. but i want index names.

quote:
Originally posted by jimf

select * from
INFORMATION_SCHEMA.KEY_COLUMN_USAGE

where column_name = 'cust_id'

Jim

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2009-07-13 : 11:00:55

select [TableName] = object_name(s.object_id)
,i.index_id
, [IndexName] = i.name

from sys.indexes i
join sys.stats s
on i.object_id = s.object_id and i.index_id = s.stats_id
join sys.columns c
on s.object_id = c.object_id
where c.name = 'risk_pk'

jim

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources