DROP TABLE Statement in SQL

DROP TABLE Statement in SQL

DROP TABLE Statement in SQL
DROP TABLE Statement in SQL

SQL DROP TABLE statement is used to remove table in a database. When you use the SQL DROP TABLE statement to remove a table, the database engine deletes all objects associated to that table including data, table structure, indexes, constraints, triggers and maybe privileges.

DROP TABLE Statement

The Drop Table command of SQL lets you drop a table from the database.


The syntax for using a Drop Table command is :

DROP TABLE <tablename>

That is, to drop a table items, you need to write :

DROP TABLE items ;

Once this command is given, the table name is no longer recognized and no more commands can be given on that object.

DROP TABLE IF EXISTS Statement

DROP TABLE IF EXISTS command of SQL lets you drop a table from the database but first it check the existence of a table in database. If the table does not exists then the DROP TABLE statement is not executed so no error occurs.

Syntax :

DROP TABLE [IF EXISTS] <tablename>

The IF EXISTS clause of DROP TABLE first checks whether the given table exists in the database or not. If it does, then it drops the mentioned table from the database.

For instance, consider the following query :

DROP TABLE IF EXISTS players ;

The above query will first check for existence of players table in current database. If it exists, then it (table players) will be dropped from the database.

Practice Question

Q 1. Write the correct SQL statement to delete a table called Student.

You can comment the answer

TRUNCATE TABLE Statement

The TRUNCATE TABLE statement is used to delete the data inside a table, but not the table itself.

Syntax

TRUNCATE TABLE table_name;

SQL DROP TABLE video

DROP TABLE Statement video credit : caleb curry

Difference between Delete, Drop & Truncate in SQL

DROP TABLE Statement video credit : Gate Smashers

Read More

MySQL Drop Database

SQL DROP TABLE Statement

Frequently Asked Questions

Q 1. How do I drop a table from a database?

DROP TABLE Statement in SQL
The syntax for using a Drop Table command is :
DROP TABLE <tablename>
That is, to drop a table items, you need to write :DROP TABLE items ;

Q 2. What is drop SQL?

DROP is used to delete a whole database or just a table. The DROP statement destroys the objects like an existing database, table, index, or view. A DROP statement in SQL removes a component from a relational database management system (RDBMS).

Q 3. How do I drop a table in SQL Server?

Using SQL Server Management Studio
In Object Explorer, select the table you want to delete.
Right-click the table and choose Delete from the shortcut menu.
A message box prompts you to confirm the deletion. Click Yes. Deleting a table automatically removes any relationships to it.
DROP TABLE Statement in SQL

Q 4. What is drop table if exists?

The DROP TABLE SQL statement enables you to delete a table from the database. The DROP TABLE IF EXISTS SQL statement enables a check to see that the table exists prior to attempting the dropping (deletion) of the tableIf the table does not exists then the DROP TABLE statement is not executed so no error occurs.
DROP TABLE Statement in SQL

Q 5. Are indexes dropped when a table is dropped?

Dropping a table removes the table definition from the data dictionary. All rows of the table are no longer accessible. All indexes and triggers associated with a table are dropped.
DROP TABLE Statement in SQL

Q 6. Can we drop primary key from a table?

Drop Primary Key

In SQL, you can drop a primary key using the ALTER TABLE statement.

Q 7. What happens when you drop a table in SQL?

When SQL Server drops a table, it also deletes all data, triggers, constraints, permissions of that table. Moreover, SQL Server does not explicitly drop the views and stored procedures that reference the dropped table.
DROP TABLE Statement in SQL

Q 8. What is difference between drop and delete?

DELETE
DELETE is a Data Manipulation Language command, DML command and is used to remove tuples/records from a relation/table.

DROP
Whereas DROP is a Data Definition Language, DDL command and is used to remove named elements of schema like relations/table, constraints or entire schema.

You may also like...

2 Responses

  1. Wow, wonderful blog structure! How long have you ever been running a blog for? you make running a blog glance easy. The entire glance of your web site is excellent, let alone the content material!!

  1. September 3, 2021

    […] DROP TABLE Statement in SQL […]

Leave a Reply

Your email address will not be published. Required fields are marked *