SQL Drop Index, Table and Database

Drop Index

You can delete an existing index in a table with the DROP INDEX statement.

Syntax for Microsoft SQLJet (and Microsoft Access):

DROP INDEX index_name ON table_name

Syntax for MS SQL Server:

DROP INDEX table_name.index_name

Syntax for IBM DB2 and Oracle:

DROP INDEX index_name

Syntax for MySQL:

ALTER TABLE table_name DROP INDEX index_name

---

Delete a Table or Database

To delete a table (the table structure, attributes, and indexes will also be deleted):

DROP TABLE table_name

To delete a database:

DROP DATABASE database_name

---

Truncate a Table

What if we only want to get rid of the data inside a table, and not the table itself? Use the TRUNCATE TABLE command (deletes only the data inside the table):

TRUNCATE TABLE table_name

SQL DELETE Statement

The DELETE Statement

The DELETE statement is used to delete rows in a table.

Syntax

DELETE FROM table_name WHERE column_name = some_value

---

Person:

LastName	FirstName	Address	City
Nilsen	Fred	Kirkegt 56	Stavanger
Rasmussen	Nina	Stien 12	Stavanger

---

Delete a Row

"Nina Rasmussen" is going to be deleted:

DELETE FROM Person WHERE LastName = 'Rasmussen'

Result

LastName	FirstName	Address	City
Nilsen	Fred	Kirkegt 56	Stavanger

---

Delete All Rows

It is possible to delete all rows in a table without deleting the table. This means that the table structure, attributes, and indexes will be intact:

DELETE FROM table_name or DELETE * FROM table_name