DROP Statement

Removes a database object from the database.

Syntax


DROP {
    [ DATABASE ] <database_name>
    | {
        [ LOGIN ] <login_name>
        | SERVER ROLE <server_role_name>
        | ROLE <database_role_name>
        | USER <user_name>
        | ASSEMBLY <assembly_name> [ FROM FILE = '<os_file_name>' ]
        | CERTIFICATE <certificate_name>
        | CONTRACT <contract_name>
        | SYNONYM <synonym_name>
        | XML SCHEMA COLLECTION <xml_schema_collection_name>
        | APPLICATION ROLE <application_role_name>
        | ASYMMETRIC KEY <asymmetric_key_name>
        | BROKER SERVICE <broker_service_name>
        | COLUMN <column_name> ON <table_name>
        | CONSTRAINT <constraint_name> ON <table_name>
        | CREDENTIAL <credential_name>
        | DATABASE AUDIT SPECIFICATION <database_audit_specification_name>
        | DATABASE SCOPED CREDENTIAL <database_scoped_credential_name>
        | ENDPOINT <endpoint_name> [ AS { USER | DATABASE } ]
        | EVENT NOTIFICATION <event_notification_name>
        | FILEGROUP <filegroup_name>
        | FUNCTION <function_name> [ ( [ @parameter [ = value ]... ] ) ]
        | MESSAGE TYPE <message_type_name>
        | PROCEDURE <procedure_name> [ ( [ @parameter [ = value ]... ] ) ]
        | QUEUE <queue_name> [ FROM <schema_name> ]
        | REMOTE SERVICE BINDING <remote_service_binding_name>
        | ROUTE <route_name>
        | SCHEMA <schema_name>
        | SEARCH PROPERTY LIST <search_property_list_name>
        | SERVICE <service_name>
        | STATISTICS <statistics_name> [ ON <table_name> ]
        | SYNCHRONIZATION SUBSCRIPTION <subscription_name>
        | TYPE <type_name>
        | USER <user_name>
        | VIEW <view_name>
        | WORKLOAD GROUP <workload_group_name>
    }
}
[ ; ]

Important Considerations

  • The DROP statement is irreversible. Once an object is dropped, it cannot be recovered without restoring from a backup.
  • Ensure you have the necessary permissions to drop an object.
  • Always back up your database before performing any significant DDL operations.

Parameters

Parameter Description
DATABASE Specifies that the entire database object is to be dropped.
LOGIN Specifies that the login is to be dropped.
<object_name> The name of the object to be dropped (e.g., table, view, procedure).
ON <table_name> Used when dropping a column, constraint, or statistics from a specific table.

Examples

Dropping a Table

This example drops a table named Sales.Customers.


DROP TABLE Sales.Customers;

Dropping a View

This example drops a view named dbo.vw_ActiveOrders.


DROP VIEW dbo.vw_ActiveOrders;

Dropping a Stored Procedure

This example drops a stored procedure named dbo.usp_GetProductDetails.


DROP PROCEDURE dbo.usp_GetProductDetails;

Dropping a Column from a Table

This example drops the EmailAddress column from the Person.Contact table.


ALTER TABLE Person.Contact
DROP COLUMN EmailAddress;

Dropping a Primary Key Constraint

This example drops a primary key constraint named PK_Product_ProductID from the Production.Product table.


ALTER TABLE Production.Product
DROP CONSTRAINT PK_Product_ProductID;

Dropping a Database

This example drops an entire database named MyOldDatabase. Use with extreme caution!


DROP DATABASE MyOldDatabase;

Permissions

To execute the DROP DATABASE statement, a user must be a member of the fixed server roles dbcreator or sysadmin.

To drop other objects, the user must have sufficient permissions on the specific object, typically CONTROL permission for the object or membership in a role that grants this permission.