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.

Also Read..

DELETE

TRUNCATE

DROP IF EXISTS

SQL Basics TutorialSQL Advance TutorialSSRSInterview Q & A
SQL Create tableSQL Server Stored ProcedureCreate a New SSRS Project List Of SQL Server basics to Advance Level Interview Q & A
SQL ALTER TABLESQL Server MergeCreate a Shared Data Source in SSRSSQL Server Question & Answer Quiz
SQL DropSQL Server PivotCreate a SSRS Tabular Report / Detail Report
..... More.... More....More
Power BI TutorialAzure TutorialPython TutorialSQL Server Tips & Tricks
Download and Install Power BI DesktopCreate an Azure storage accountLearn Python & ML Step by stepEnable Dark theme in SQL Server Management studio
Connect Power BI to SQL ServerUpload files to Azure storage containerSQL Server Template Explorer
Create Report ToolTip Pages in Power BICreate Azure SQL Database ServerDisplaying line numbers in Query Editor Window
....More....More....More

 

 491 total views,  1 views today

Leave a Reply

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