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

User · Answer

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

User · Answer

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

User · Answer

A slight deviation from the original question however may prove useful for future people landing here wanting to DROP and CREATE an index  i e  in a deployment script   You can bypass the exists check simply by adding the following to your create statement   CREATE INDEX IX IndexName ON dbo TableName WITH  DROP EXISTING   ON     Read more here  CREATE INDEX  Transact-SQL  - DROP EXISTING Clause  N B  As mentioned in the comments  the index must already exist for this clause to work without throwing an error

User · Answer

Wrote the below function that allows me to quickly check to see if an index exists  works just like OBJECT ID   CREATE FUNCTION INDEX OBJECT ID        tableName VARCHAR 128        indexName VARCHAR 128        RETURNS INT AS BEGIN     DECLARE  objectId INT      SELECT  objectId   i object id     FROM sys indexes i     WHERE i object id   OBJECT ID  tableName      AND i name    indexName      RETURN  objectId END GO   EDIT  This just returns the OBJECT ID of the table  but it will be NULL if the index doesn t exist   I suppose you could set this to return index id  but that isn t super useful

User · Answer

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

User · Answer

To check Clustered Index exist on particular table or not   SELECT   FROM SYS indexes  WHERE index id   1 AND name IN  SELECT CONSTRAINT NAME FROM INFORMATION SCHEMA TABLE CONSTRAINTS WHERE TABLE NAME    Table Name

User · Answer

-- Delete index if exists IF EXISTS SELECT TOP 1 1 FROM sys indexes indexes INNER JOIN sys objects  objects ON indexes object id   objects object id WHERE indexes name    Your Index Name  AND objects name    Your Table Name   BEGIN     PRINT  DROP INDEX  Your Index Name  ON  dbo   Your Table Name       DROP INDEX  our Index Name  ON  dbo   Your Table Name  END GO

User · Answer

If the hidden purpose of your question is to DROP the index before making INSERT to a large table  then this is useful one-liner   DROP INDEX IF EXISTS  IndexName  ON  dbo   TableName    This syntax is available since SQL Server 2016  Documentation for IF EXISTS   https   blogs msdn microsoft com sqlserverstorageengine 2015 11 03 drop-if-exists-new-thing-in-sql-server-2016   In case you deal with a primery key instead  then use this   ALTER TABLE  TableName  DROP CONSTRAINT IF EXISTS  PK name

[sql-server] How do you check if a certain index exists in a table?

Examples related to sql-server