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 tutorialIF EXISTS:
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 tutorialExample:
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 tutorialDifference Between SQL, MySQL and Oracle Drop Statement
Difference Between SQL, MySQL and Oracle Drop Statement
