Many a time we come across a scenario where we need to execute some code based on whether an Index (Clustered/Non-Clustered) exists or not. This article explains how we can check the existence of Index with extensive list of examples.

[ALSO READ] How to find all the tables with no indexes at all in Sql Server?

To demonstrate how we can check the existence of a Index, let us create a sample demo database with a Customer table having a Clustered and Non-Clustered indexes by executing the following script:

--Create Demo Database
CREATE DATABASE SqlHintsIndexExists
GO
USE SqlHintsIndexExists
GO
--Create Customer Table
CREATE TABLE dbo.Customer
(
	Id INT NOT NULL, Name NVARCHAR(100)
)
GO
--Create Clustered Index IX_Customer_Id
CREATE CLUSTERED INDEX IX_Customer_Id ON dbo.Customer(Id) 
GO
--Create Non-Clustered Index PK_Customer
CREATE NONCLUSTERED INDEX IX_Customer_Name 
    ON dbo.Customer(Name) 

Executing the above script creates a Customer table with a Clustered and a Non-Clustered indexes as shown in the below image:

Let us now understand the various approaches of checking the existence of an index:

[ALSO READ] How to get all the Tables with or without Non-Clustered Indexes in Sql Server?

Approach 1: Check the existence of Index by using catalog views

sys.indexes catalog view a record for each Clustered and Non-Clustered indexes. We can execute a query like below to check the existence of a Clustered Index IX_Customer_Id on the Customer table created with a default schema (i.e. dbo).

IF EXISTS (SELECT 1
			FROM sys.indexes I
				INNER JOIN sys.tables T
					ON I.object_id = T.object_id
				INNER JOIN sys.schemas S
					ON S.schema_id = T.schema_id
			WHERE I.Name = 'IX_Customer_Id' -- Index name
				AND T.Name = 'Customer' -- Table name
				AND S.Name = 'dbo') --Schema Name
BEGIN
	PRINT 'Index Exists!'
END

RESULT:

[ALSO READ] How to find all the filtered indexes or all the tables having filtered indexes in Sql Server?

We can execute a query like below to check the existence of a Non-Clustered Index IX_Customer_Name on the Customer table created with a default schema (i.e. dbo). This query is same as the previous query only difference is the name of the index passed to it.

IF EXISTS (SELECT 1
			FROM sys.indexes I
				INNER JOIN sys.tables T
					ON I.object_id = T.object_id
				INNER JOIN sys.schemas S
					ON S.schema_id = T.schema_id
			WHERE I.Name = 'IX_Customer_Name' -- Index name
				AND T.Name = 'Customer' -- Table name
				AND S.Name = 'dbo') --Schema Name
BEGIN
	PRINT 'Index Exists!'
END

RESULT:

[ALSO READ] How to get all HEAP Tables or Tables without Clustered Index in Sql Server?

Approach 2: Check the existence of Index by using sys.indexes catalog view and OBJECT_ID function

We can execute a query like below to check the existence of a Clustered Index IX_Customer_Id on the Customer table created with a default schema (i.e. dbo).

IF EXISTS (SELECT 1
			FROM sys.indexes I				
			WHERE I.Name = 'IX_Customer_Id' -- Index name
			 AND I.object_id = OBJECT_ID('dbo.Customer'))
BEGIN
	PRINT 'Index Exists!'
END

RESULT:

[ALSO READ] How to find all the indexes that have included columns in it and the name of the table to which the index belongs to?

We can execute a query like below to check the existence of a Non-Clustered Index IX_Customer_Name on the Customer table created with a default schema (i.e. dbo). This query is same as the previous query only difference is the name of the index passed to it.

IF EXISTS (SELECT 1
	   FROM sys.indexes I				
	   WHERE I.Name = 'IX_Customer_Name' -- Index name
	    AND I.object_id = OBJECT_ID('dbo.Customer'))
BEGIN
	PRINT 'Index Exists!'
END

RESULT:

[ALSO READ]
How to check if a Database exists in Sql Server
How to check if a Table exists in Sql Server
How to check if Temp table exists in Sql Server
How to check if a Stored Procedure exists in Sql Server
How to check if a Function exists in Sql Server
How to check if a VIEW exists in Sql Server
How to check if a Trigger exists in Sql Server
How to check if a record exists in table in Sql Server