Query to check the existence of index on a 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

SQL Server Administration (2005)

Query to check the existence of index on a column

Author

Topic

venkath
Posting Yak Master

202 Posts

Posted - 2009-10-05 : 04:10:23

Hi All

Is there a query to check whether the column in a table has index defined on it or not

SP_HELPDB gives information on all indexes on all columns. I would like to know these details only for a column provided in the query

Thanks

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2009-10-05 : 05:07:13

select * from sys.indexes where object_id=object_id('table_name')
and type=2 (1 for clustered index)

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

venkath
Posting Yak Master

202 Posts

Posted - 2009-10-05 : 05:32:09

This query is returning all the indexes on a particular table similat to what SP_HELPINDEX does.

I was looking for a query to check for only a particular column..

Thanks.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-10-05 : 05:47:29

If exists(
select c.name from sys.index_columns as ic inner join
sys.columns as c on ic.object_id=c.object_id and ic.column_id=c.column_id
where c.object_id=object_id('table_name') and c.name='column_name'
)
print 'column has index'
else
print 'column doesn''t has index'

Madhivanan

Failing to plan is Planning to fail

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2009-10-05 : 05:50:30

Try this

select k.*, x.name
from sys.indexes x
join sysindexkeys k on k.id = x.object_id
join sys.columns c on c.object_id = x.object_id and k.colid=c.column_id
where x.object_id = object_id('table_name')
and c.name='column_name'

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

venkath
Posting Yak Master

202 Posts

Posted - 2009-10-05 : 05:59:19

Thank you all.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources