How to Drop an Index in SQL Server in SQL Server

To drop a non-primary key index, use the DROP INDEX command:

-- syntax
drop index table_name.index_name;

drop index products.products_category;

To drop a primary key index, use the ALTER TABLE .. DROP CONSTRAINT command :

alter table products
drop constraint pk_products;