Home » SQL DROP TABLE

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 SERVER.

First we create a tabloe 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.

 

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 have just dropped a table above.

Just 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 Lets 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.

 

DELETE

TRUNCATE

 

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.