SQL DROP TABLE

SQL DROP TABLE statement is used to delete the table from database. It removes a table definition, constraints, indexes, triggers and any permission specifications for that table.

Once, you drop a table then all the information related to table will be lost forever and you can not get back records.

• DROP command can be rolled back.
• DROP command does not support WHERE CLAUSE.

SYNTAX

DROP TABLE table_name

Lets look at an example of DROP statement in SQL.

First, we create a table named SALES and insert some records into this table as shown below.

CREATE TABLE dbo.SALES
(SALES_ID INT IDENTITY(1,1)  , 
ITEM VARCHAR(50), 
ITEM_CODE VARCHAR(15),
ITEM_QTY INT,
PURCHASED_DATE DATE
)

INSERT INTO dbo.SALES
(ITEM, ITEM_CODE, ITEM_QTY, PURCHASED_DATE)
VALUES('LAPTOP- HP','PAVILLION-XCV89', 500,'2019-04-05'),
('KEYBOARD','KEYB-567', 100,'2019-05-19'),
('MOUSE','MM-ER56', 250,'2019-05-15'),
('PEN DRIVE','PD-20ET', 400,'2019-07-25'),
('RADEON GRAPHIC CARD',null, 478,'2019-09-19') 

Now, we have a table named as SALES as shown below.

SELECT * FROM dbo.SALES

DROP table from database

Following statements, drops the table SALES from database.

DROP TABLE dbo.SALES

You can check, whether the table is dropped from database or not just select the table.

SELECT * FROM dbo.SALES

You can see when you select a table SALES, It gives you an error saying ‘Invalid object name ‘dbo.SALES’.

That means table has been dropped from database successfully.

DROP table command can not be rolled back

Drop table can not be rolled back,  to demonstrate this lets re-create a table again as we just dropped a table above.

You can re execute the create table and insert scripts again.

CREATE TABLE dbo.SALES
(SALES_ID INT IDENTITY(1,1)  , 
ITEM VARCHAR(50), 
ITEM_CODE VARCHAR(15),
ITEM_QTY INT,
PURCHASED_DATE DATE
)

INSERT INTO dbo.SALES
(ITEM, ITEM_CODE, ITEM_QTY, PURCHASED_DATE)
VALUES('LAPTOP- HP','PAVILLION-XCV89', 500,'2019-04-05'),
('KEYBOARD','KEYB-567', 100,'2019-05-19'),
('MOUSE','MM-ER56', 250,'2019-05-15'),
('PEN DRIVE','PD-20ET', 400,'2019-07-25'),
('RADEON GRAPHIC CARD',null, 478,'2019-09-19')

Now, execute the drop command under transaction block.

BEGIN TRAN
SELECT * FROM dbo.SALES
DROP TABLE dbo.SALES
SELECT * FROM dbo.SALES
ROLLBACK TRAN
SELECT * FROM dbo.SALES

 

As you can see, once you drop a table from database you can not get it back. It gives you a an error saying Invalid object name ‘dbo.SALES’.

Also Read..

DELETE

TRUNCATE

DROP IF EXISTS