Friday, March 23, 2012

How to test if Index exists?

How can I test if a table already has a specific index present?
Id normally test for an object with: if object_ID('My Object') is null then...
What technique works with an index? Or can you use the object_Id function, how do you reference the index?
Many thanks.You can check if a table has an index at all, not a specific index, unless you want to test for an existance of the name:

if exists (select 1 from dbo.sysindexes where object_name(id)='your_table' and indid between 2 and 254) print 'There is at least 1 non-clustered index'
else print 'No non-clustered indexes found'|||IF INDEXPROPERTY ( OBJECT_ID('your_table') , 'your_table_index' , 'IndexID' ) IS NULL

If you know the name the above should work; I have not tested it.

Tim S|||sp_helpindex tableName

OR

IF EXISTS (SELECT indid
FROM sysindexes
WHERE id = OBJECT_ID('tableName')
AND name = 'indexName') THEN

....sql

No comments:

Post a Comment