List and Drop Table

📋 Listing All Tables in a Database

A database schema contains metadata about all objects in the database, including tables, views, and indexes. If you want to see only the tables in your database, you can query the schema for objects of type table.

Suppose you have a database with the following tables:

SQL Table

Tables in Database

| Table Name   |
|--------------|
| past         |
| events       |
| crew         |

SQL Code

SELECT name 
FROM sqlite_schema 
WHERE type = 'table';

Output

name
past
events
crew

tip

✅ Tip: Querying the schema is a reliable way to list all tables in your SQLite database.

🎯 Example: Listing Table Names Only

To list all tables in your database, you only need the name column from the schema. Use a SELECT statement to retrieve just the table names.

Sample Data

name	entry	attendees
Pride Party	10	79
Classical Day	15	76
Wine tasting	8	43

SQL Code

SELECT name
FROM sqlite_schema
WHERE type = 'table';

To get only the table names, select the name column from sqlite_schema where type is 'table'.

🗑️ Deleting a Table from the Database

Once you have listed all tables, you may want to remove an entire table from your database. You can do this using the DROP TABLE statement. For example, to delete the past_events table:

Sample Data

name	entry	attendees
Pride Party	10	79
Classical Day	15	76
Wine tasting	8	43

SQL Code

DROP TABLE past_events;
SELECT name FROM sqlite_schema WHERE type = 'table';

Output

name
events
crew

caution

⚠️ Caution: Dropping a table will permanently delete the table and all its data. Make sure you have backups if needed.

✅ What You Have Learned

In this module, you learned how to manage tables in your SQL database, including:

• Listing Tables
  Querying the database schema to view all tables present in your database.

• Dropping Tables
  Using the DROP TABLE statement to permanently remove tables and their data.

• Best Practices
  Always verify before dropping tables, as this action cannot be undone, and ensure you have backups if needed.

These skills are essential for maintaining and organizing your database as your project requirements change.

---

📝 Quiz: Test Your Knowledge

1. Now that we have the list of tables, let's delete the entire past_events table with the DROP TABLE query.

Here is the original past_events table:

name	entry	attendees
Pride Party	10	79
Classical Day	15	76
Wine tasting	8	43

Answer

• Use the DROP TABLE statement to remove the entire table:

DROP TABLE past_events;
SELECT name FROM sqlite_schema WHERE type = 'table';

---

2. Which of the following statements is true for a schema?

Q Tap the correct answer

• A schema contains only table information.
• A schema contains information about a database.

Answer

• A schema contains information about a database, including tables, views, indexes, and other objects.

3. Where is the list of tables stored in your database?

Q Tap the correct answer

• In the data rows of a table
• In the schema

Answer

• In the schema , The list of tables is stored in the database schema, which contains metadata about all objects in the database.