Drop table | Drop table statement in sql - sql - sql tutorial - learn sql

Define Drop Table Statement?

• The SQL DROP TABLE statement is used to remove a table definition and all the data, indexes, triggers, constraints and permission specifications for that table.
• Sometimes we need to get rid of a table in the database.
• In fact, it would be problematic if we cannot do so because this could create a maintenance nightmare for the DBA's.
• Fortunately, SQL allows us to do it, as we can use the DROP TABLE command.
• NOTE − You should be very careful while using this command because once a table is deleted then all the information available in that table will also be lost forever.

• The syntax for DROP TABLE is,

DROP TABLE "table_name";

• If we wanted to drop the Customer table that we created created in the CREATE TABLE section, we simply type,

DROP TABLE wikitechy Customer;

Drop Multiple Tables at The Same Time:

• It is possible to drop more than one table at a time.
• To do that, list the names of all the tables we wish to drop separated by comma after DROP TABLE.
• For example, if we want to drop the User_Details table and the Job_List table together, we can issue the following SQL statement:

  DROP TABLE User_Details, Job_List;

Tags : sql tutorial , pl sql tutorial , mysql tutorial , oracle tutorial , learn sql , sql server tutorial

IF EXISTS:

• When we attempt to drop a table that does not exist, an error will result.
• To prevent this type of error from happening, some databases such as MySQL and Oracle allow an optional "IF EXISTS" phrase between DROP TABLE and the table name(s).
• This tells the database to execute the DROP TABLE statement only if the table to be dropped already exists.
• If the table does not exist, nothing is executed and there is no error message. The following is an example of a DROP TABLE IF EXISTS statement:

DROP TABLE IF EXISTS Customer;

sql tutorial , pl sql tutorial , mysql tutorial , oracle tutorial , learn sql , sql server tutorial

Example:

• Let's look at an example that shows how to drop a table using the DROP TABLE statement in SQL Server (Transact-SQL).

Example 1:

DROP TABLE employees;

• This DROP TABLE example would drop the table called employees from the current database in SQL Server.
• What happens if the table that you wish to delete is not in the current database, but rather in another database in SQL Server? Let's look at how to drop a table in another database.

Example 2:

DROP TABLE Database2.dbo.suppliers;

• This DROP TABLE example would drop the table called suppliers that is not in the current database in SQL Server.
• Rather, the suppliers table is found in another database called Database2 on the SQL Server instance.
• Since we are referencing the database name, this DROP TABLE statement could be executed from any database on the server instance (provided that you have the required DROP privileges).
• Next, let's look at how to drop a LOCAL TEMPORARY TABLE using the DROP TABLE statement.
• First it is important to note that all LOCAL TEMPORARY TABLES are prefixed with the # character.

Example 3:

 DROP TABLE #employees;

• This DROP TABLE example would drop the LOCAL TEMPORARY TABLE called #employees.
• Finally, let's look at how to drop a GLOBAL TEMPORARY TABLE using the DROP TABLE statement.
• While LOCAL TEMPORARY TABLES are prefixed with the # character, GLOBAL TEMPORARY TABLES start with ## characters.

Example 4:

DROP TABLE ##suppliers;

• This DROP TABLE example would drop the GLOBAL TEMPORARY TABLE called ##suppliers.

sql tutorial , pl sql tutorial , mysql tutorial , oracle tutorial , learn sql , sql server tutorial

Difference Between SQL, MySQL and Oracle Drop Statement

This tutorial provides more the basic needs and informations on sql tutorial , pl sql tutorial , mysql tutorial , sql server , sqlcode , sql queries , sql , sql formatter , sql join , w3schools sql , oracle tutorial , mysql , pl sql , learn sql , sql tutorial for beginners , sql server tutorial , sql query tutorial , oracle sql tutorial , t sql tutorial , ms sql tutorial , database tutorial , sql tutorial point , oracle pl sql tutorial , oracle database tutorial , oracle tutorial for beginners , ms sql server tutorial , sql tutorial pdf