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

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

Related Searches to Drop table | Drop table statement in sql