SQL Server - DROP Keyword

The SQL Server (Transact-SQL) DROP keyword is used to delete a COLUMN, CONSTRAINT, DATABASE, INDEX, TABLE, or VIEW.

DROP COLUMN

To drop the Age column from table called Employee, the following statement can be used:

ALTER TABLE Employee
DROP COLUMN Age;

DROP a UNIQUE Constraint

To drop UC_Employee UNIQUE constraint from table called Employee, the below mentioned statement can be used:

ALTER TABLE Employee
DROP CONSTRAINT UC_Employee;

DROP a PRIMARY KEY Constraint

To drop PK_Employee PRIMARY KEY constraint from table called Employee, the statement is given below:

ALTER TABLE Employee
DROP CONSTRAINT PK_Employee;

DROP a FOREIGN KEY Constraint

To drop FK_Contact_Info FOREIGN KEY constraint from table called Contact_Info, the statement is given below:

ALTER TABLE Contact_Info
DROP CONSTRAINT FK_Contact_Info;

DROP a CHECK Constraint

To drop CHK_Employee CHECK constraint from table called Employee, the statement is given below:

ALTER TABLE Employee
DROP CONSTRAINT CHK_Employee;

DROP DEFAULT constraint

To drop DEFAULT constraint from City column of Employee table, the following statement can be used:

/* Drops a DEFAULT constraint named DV_City */
ALTER TABLE Employee
DROP CONSTRAINT DV_City;

DROP INDEX

Consider a table called Employee which contains an index named idx_name. To drop this index, the following statement can be used:

/* Method 1 */
DROP INDEX idx_name ON Employee;

/* Method 2 */
ALTER TABLE Employee
DROP INDEX idx_name;

/* Method 3 */
DROP INDEX Employee.idx_name;

/* Method 4 */
DROP INDEX idx_name;

DROP DATABASE

To drop an existing database with name testDB, the following statement can be used:

DROP DATABASE testDB;

DROP TABLE

To drop a table name Employee, the following statement can be used:

DROP TABLE Employee;

DROP VIEW

To drop EmployeeInIndia view from table called Employee, the below statement can be used:

DROP VIEW EmployeeInIndia;