How do you check if a certain index exists in a table?

Question

Something like this:

SELECT
* 
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS 
WHERE CONSTRAINT_NAME ='FK_TreeNodesBinaryAssets_BinaryAssets'
and TABLE_NAME = 'TreeNodesBinaryAssets'

but for indexes.

Answer 1

You can do it using a straight forward select like this:

SELECT * 
FROM sys.indexes 
WHERE name='YourIndexName' AND object_id = OBJECT_ID('Schema.YourTableName')

Answer 2

For SQL 2008 and newer, a more concise method, coding-wise, to detect index existence is by using the INDEXPROPERTY built-in function:

INDEXPROPERTY ( object_ID , index_or_statistics_name , property )  

The simplest usage is with the IndexID property:

If IndexProperty(Object_Id('MyTable'), 'MyIndex', 'IndexID') Is Null

If the index exists, the above will return its ID; if it doesn't, it will return NULL.

Answer 3

AdaTheDEV, I used your syntax and created the following and why.

Problem: Process runs once a quarter taking an hour due to missing index.

Correction: Alter query process or Procedure to check for index and create it if missing... Same code is placed at the end of the query and procedure to remove index since it is not needed but quarterly. Showing Only drop syntax here

-- drop the index 
begin

  IF EXISTS (SELECT *  FROM sys.indexes  WHERE name='Index_Name' 
    AND object_id = OBJECT_ID('[SchmaName].[TableName]'))
  begin
    DROP INDEX [Index_Name] ON [SchmaName].[TableName];
  end

end