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.
Also Read..
2,140 total views, 1 views today