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')
SQLServerCentral
sqlservercentral.com › home › topics › if exists drop index ... if not exists create index
IF EXISTS DROP INDEX ... IF NOT EXISTS CREATE INDEX – SQLServerCentral Forums
August 13, 2012 - IF EXISTS (SELECT 1 FROM sys.indexes WHERE name = 'Index name here' AND object_id = OBJECT_ID('Table Name Here') DROP INDEX .... CREATE INDEX ... Gail Shaw Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci) SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Top answer 1 of 9
619
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')
2 of 9
155
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.
if exists drop index - SQL Server Forums
Microsoft SQL Server articles, forums and blogs for database administrators (DBA) and developers. More on sqlteam.com
sql - Drop indexes or alter - Stack Overflow
The parser is seeing you try to drop an index that doesn't exist, but not smart enough to follow your IF logic. Use dynamic SQL (IF EXISTS (...) EXEC sys.sp_executesql N'DROP INDEX ...';). More on stackoverflow.com
Drop Index if exists syntax error
In CB Server 7, I can find no way to use the IF EXISTS clause when dropping an index without a syntax error: DROP INDEX idx1 IF EXISTS ON bucket - fails DROP INDEX IF EXISTS idx1 ON bucket - fails DROP INDEX idx1 ON bucket IF EXISTS - fails DROP INDEX(idx1) IF EXISTS ON bucket - fails DROP ... More on couchbase.com
3
0
May 31, 2022Create Index Question
This is the most straight forward way: If not exists (select * from sys.indexes where object_id = object_id('myTable') and name= 'myIndex') Create index myIndex on myTable... More on reddit.com
11
6
December 18, 2024Microsoft Learn
learn.microsoft.com › en-us › sql › t-sql › statements › drop-index-transact-sql
DROP INDEX (Transact-SQL) - SQL Server | Microsoft Learn
Applies to: SQL Server 2016 (13.x) and later versions. Conditionally drops the index only if it already exists.
MSSQLTips
mssqltips.com › home › new drop if exists syntax in sql server 2016
New Drop If Exists Syntax in SQL Server 2016
August 22, 2016 - It is a brand new feature released in SQL Server 2016. The new DROP IF EXISTS optional clause has been added to twenty data definition language statements. This syntax reduces the amount of code that used to be required to create a T-SQL script that is truly restart able.
GeeksforGeeks
geeksforgeeks.org › sql › sql-drop-index
SQL DROP INDEX Statement - GeeksforGeeks
Now let's look at some examples of DROP INDEX statement and understand its workings in SQL. ... The IF EXISTS clause drops the index only if it exists, preventing errors when the index is not found.
Published June 19, 2026
Kendralittle
kendralittle.com › 2016 › 01 › 28 › how-to-check-if-an-index-exists-on-a-table-in-sql-server
How to Check if an Index Exists on a Table in SQL Server
January 28, 2016 - Msg 7999, Level 16, State 9, Line 1 Could not find any index named ‘ix_halp’ for table ‘agg.FirstNameByYear’. The DROP_EXISTS clause is only for changing the definition of an index that already exists.
SQL Team
sqlteam.com › forums › topic.asp
if exists drop index - SQL Server Forums
February 8, 2008 - Microsoft SQL Server articles, forums and blogs for database administrators (DBA) and developers.
Tutorialspoint
tutorialspoint.com › sql › sql-drop-index.htm
SQL - Drop Index
The DROP INDEX IF EXISTS statement in SQL is used to drop an index only if it exists in the table. This statement is specifically useful when you want to drop an index, but you are not sure if the index exists.
Sqlespresso
sqlespresso.com › home › performance tuning › sql index creation using drop existing= on
SQL Index Creation Using DROP EXISTING= ON - A Shot of SQLEspresso
February 17, 2021 - DROP INDEX IF EXISTS [dcacIDX_ServiceType] ON [dbo].[Accounts] GO CREATE NONCLUSTERED INDEX [dcacIDX_ServiceType] ON [dbo].[Accounts] ( [ServiceType] ASC ) INCLUDE([AccountId] WITH (STATISTICS_NORECOMPUTE = OFF, DROP_EXISTING = OFF, ONLINE = OFF, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY] GO · Now I should also note that the DROP_EXISITING method is also faster when you must modify a Clustered index. Every Non-Clustered index refers to the Clustered index using what is called a clustering key, essentially, a pointer to the row in the clustered index. When a clustered index is dropped and re-created, SQL Server must rebuild the Non-Clustered indexes on that table.
Top answer 1 of 2
12
The likely cause for this is that you might have more than one index named xyz on your database. Your IF EXISTS SELECT statement is not taking into consideration which table xyz is on.
You can check for this condition yourself by running the select statement by itself.
Try changing your query to the following to limit the scope:
If Exists
(
Select *
From sys.indexes
Where name = 'xyz'
And Object_Id = Object_Id('dbo.Table_Name')
)
Drop Index xyz On dbo.Table_Name;
2 of 2
2
One way to get around this issue is to trick the parser:
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE SCHEMA_NAME = 'dbo' AND TABLE_NAME = 'Table_Name' AND CONSTRAINT_TYPE = 'PRIMARY KEY' AND CONSTRAINT_NAME = 'xyz')
BEGIN
EXEC('ALTER TABLE [dbo].[Table_Name] DROP CONSTRAINT [xyz]')
END
PostgreSQL
postgresql.org › docs › current › sql-dropindex.html
PostgreSQL: Documentation: 18: DROP INDEX
May 14, 2026 - DROP INDEX DROP INDEX — remove an index Synopsis DROP INDEX [ CONCURRENTLY ] [ IF EXISTS ] name [, …
MariaDB
mariadb.com › docs › server › reference › sql-statements › data-definition › drop › drop-index
DROP INDEX | Server | MariaDB Documentation
1 month ago - This command deletes the index structure, potentially impacting query performance but freeing storage. Copy · DROP INDEX [IF EXISTS] index_name ON tbl_name [WAIT n |NOWAIT] DROP INDEX drops the index named index_name from the table tbl_name.
Couchbase
couchbase.com › couchbase server
Drop Index if exists syntax error - Couchbase Server - Couchbase Forums
May 31, 2022 - In CB Server 7, I can find no way to use the IF EXISTS clause when dropping an index without a syntax error: DROP INDEX idx1 IF EXISTS ON bucket - fails DROP INDEX IF EXISTS idx1 ON bucket - fails DROP INDEX idx1 ON bucket IF EXISTS - fails ...
jOOQ
jooq.org › doc › latest › manual › sql-building › ddl-statements › drop-statement › drop-index-statement › drop-index-if-exists
The IF EXISTS clause of the DROP INDEX statement
The IF EXISTS clause of the DROP INDEX statement is used to drop an index only if it already exists in the database.
Oracle
docs.oracle.com › en › database › other-databases › nosql-database › 24.3 › sqlreferencefornosql › drop-index.html
DROP INDEX Statement
November 11, 2024 - The DROP INDEX removes the specified index from the database. ... If an index with the given name does not exist, then the statement fails, and an error is reported.
Troy Hunt
troyhunt.com › 2-billion-email-addresses-were-exposed-and-we-indexed-them-all-in-have-i-been-pwned
Troy Hunt: 2 Billion Email Addresses Were Exposed, and We Indexed Them All in Have I Been Pwned
November 14, 2025 - Taking 2 billion records and adding ... in the existing 15 billion corpus, whilst not adversely impacting the live system serving millions of visitors a day, was very non-trivial. Managing the nuances of SQL Server indexes such that we could optimise both inserts and queries is not my idea of fun, and it's been a pretty hard couple of weeks if I'm ...
W3C
w3.org › TR › webdatabase
Web SQL Database
Otherwise, if the mode that was used to create the SQLTransaction or SQLTransactionSync object is read-only but the statement's main verb can modify the database, mark the statement as bogus. (Error code 5.) Only the statement's main verb (e.g. UPDATE, SELECT, DROP) is considered here.
Neon
neon.com › postgresql › tutorial
PostgreSQL Tutorial
Finally, you’ll learn how to manage database tables, including creating a new table or modifying the structure of an existing table. Select – show you how to query data from a single table. Column aliases – learn how to assign temporary names to columns or expressions within a query. Order By – guide you on how to sort the result set returned by a query. Select Distinct – show you how to remove duplicate rows from the result set. Where – filter rows based on a specified condition. AND operator – combine two boolean expressions and return true if both expressions evaluate to true.
Database Guide
database.guide › how-to-delete-an-index-in-sql-server
How to Delete an Index in SQL Server
August 11, 2024 - To remove the constraint and corresponding index, use ALTER TABLE with the DROP CONSTRAINT clause. So to drop such indexes, we need to drop the constraint itself, which might go something like this: ALTER TABLE Employees DROP CONSTRAINT IF EXISTS UQ_Employees_Email;
Drizzle ORM
orm.drizzle.team › docs › overview
Drizzle ORM - Why Drizzle?
It’s the only ORM with both relational and SQL-like query APIs, providing you the best of both worlds when it comes to accessing your relational data. Drizzle is lightweight, performant, typesafe, non-lactose, gluten-free, sober, flexible and serverless-ready by design.