Verify DROP INDEX
To verify if the DROP INDEX statement has successfully removed the index from the table, we can check the indexes on the table. If the index is not present in the list, we know it has been deleted.
Syntax
The syntax for viewing the index on a table differs for different databases, for example:
SQL Server:
SELECT * FROM sys.indexes WHERE object_id = (SELECT object_id FROM sys.objects WHERE name = 'YOUR_TABLE_NAME')
MySQL:
SHOW INDEXES FROM YOUR_TABLE_NAME;
PostgreSQL:
SELECT * FROM USER_INDEXES;
Oracle:
SELECT
indexname, indexdef
FROM
pg_indexes
WHERE
tablename = 'your_table_name';
SQL DROP INDEX Statement
The SQL DROP INDEX statement removes an existing Index from a database table.