Dropping All Database Tables in One SQL Query

1. Introduction

Dropping all tables within a specific schema is a common practice, especially in test and development environments. Consequently, this procedure is frequently utilized to reset the database to a clean state.

In this article, we’ll learn how to delete all the tables inside a schema in a database.

2. Setup

For demo purposes, we’ll create some sample tables inside the sample schema (vendor_a):

sample_db=# \dt+
                                          List of relations
  Schema  |     Name     | Type  |  Owner   | Persistence | Access method |    Size    | Description
----------+--------------+-------+----------+-------------+---------------+------------+-------------
 vendor_a | credit_cards | table | postgres | permanent   | heap          | 0 bytes    |
 vendor_a | transactions | table | postgres | permanent   | heap          | 8192 bytes |
 vendor_a | users        | table | postgres | permanent   | heap          | 8192 bytes |
(3 rows)

3. Dropping All the Tables

The easiest way to drop all the tables would be to get the table names by giving schema names and then drop each individually. We can write a script that would do the same.

information_schema.tables is a system view provided by many relation database management systems like MySQL, PostgreSQL, SQL Server, etc. This view stores metadata about database objects.

We’ll use information_schema.tables to get all the tables inside a schema vendor_a, and then we’ll loop through each table and execute a drop statement if the table exists in the database.

3.1. PostgreSQL

In PostgreSQL, we can write a PL/pgSQL script to drop all the tables inside a schema:

DO $$
DECLARE
    drop_statement text;
BEGIN
    FOR drop_statement IN
        SELECT 'DROP TABLE IF EXISTS "' || table_name || '" CASCADE;'
        FROM information_schema.tables
        WHERE table_schema = 'vendor_a'
    LOOP
        EXECUTE drop_statement;
    END LOOP;
END $$;

In the above script, we use the FOR loop in PL/pqSQL, for iterating over each table in the vendor_a schema, and then we generate the DROP TABLE statement with CASCADE  to drop any dependent objects like sequence, views, and triggers.

We can verify the result for the above script by executing \dt+ in the psql terminal or querying information_schema.tables.

sample_db=# \dt+ 
Did not find any relations

SELECT table_schema, table_name
FROM information_schema.tables 
WHERE table_schema = 'vendor_a';

 table_schema | table_name
--------------+------------
(0 rows)

As we can see, our script works as expected.

3.2. SQL Server

In SQL Server, we can write a T-SQL (Transact-SQL) script to drop all the tables inside a schema:

DECLARE @sql NVARCHAR(MAX) = '';
SELECT @sql += 'DROP TABLE ' + QUOTENAME(table_schema) + '.' + QUOTENAME(table_name) + ';' + CHAR(13)
FROM information_schema.tables
WHERE table_type = 'base table'
AND table_schema = 'vendor_a';

EXEC sp_executesql @sql;

In the above T-SQL script, we are declaring the @sql variable which stores dynamically generated DROP statements. To execute this dynamically generated SQL, T-SQL provides stored procedures like sp_executesql.

We can verify the result of the above script by querying information_schema.tables:

SELECT table_schema, table_name
FROM information_schema.tables
WHERE table_schema = 'vendor_a';

 table_schema | table_name
--------------+------------
(0 rows affected)

As we can see, our script works as expected.

3.3. MySQL

MySQL supports writing scripts to drop all the tables inside a schema:

SET @schema_name = 'vendor_a';

SELECT @drop_statements := GROUP_CONCAT(CONCAT('DROP TABLE IF EXISTS `', table_name, '`') SEPARATOR '; ')
FROM information_schema.tables
WHERE table_schema = @schema_name;

IF @drop_statements IS NOT NULL THEN
    PREPARE stmt FROM @drop_statements;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
END IF;

In the above script, we are declaring @schema_name to store the schema name and @drop_statement to store dynamically generated drop statements. GROUP_CONCAT function is used to concatenate multiple DROP statements into a single string. At the end, we execute the prepared statement.

We can verify the result of the above script by querying information_schema.tables:

SELECT table_schema, table_name
FROM information_schema.tables
WHERE table_schema = 'vendor_a';

Empty set (0.17 sec)

As we can see, our script works as expected.

4. Conclusion

In this article, we discussed the approach of dropping all the tables in a schema using SQL script with its use cases. Specifically, we implemented the core logic using various db-specific scripting languages like  PL/pgSQL and T-SQL.

This approach exemplifies how understanding and leveraging SQL capabilities can simplify complex tasks, making database management more streamlined and less error-prone.