How to drop all tables from a database with one SQL query?

sql

database-management

sql-queries

data-architecture

byNikita Barsukov·Jan 3, 2025

Instantly drop all tables from a SQL Server database by generating a set of DROP TABLE commands and executing them. It's done by harvesting details from the system tables. Here's a prescriptive example:

DECLARE @Sql NVARCHAR(MAX) = '';
-- Looping over each table to create a command to exterminate... I mean, drop it.
SELECT @Sql += 'DROP TABLE [' + TABLE_SCHEMA + '].[' + TABLE_NAME + '];' FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE';
EXEC sp_executesql @Sql; -- It's not a crime scene if no one is around to witness it.

Don't forget to create a backup of your data before running this command. This procedure will erase all tables permanently.

Dealing with foreign keys

When prepping the tables for their DROP TABLE execution, remember to first disable any foreign key constraints. This will prevent errors due to any inter-table dependencies. Here is how you can do it with sp_msforeachtable stored procedure:

EXEC sp_msforeachtable 'ALTER TABLE ? NOCHECK CONSTRAINT all'; -- "I've got NOCHECKs given for these constraints!"
EXEC sp_MSforeachtable 'DROP TABLE ?'; -- Table dropping party starts NOW!

Heads up! sp_msforeachtable might not adhere to the exact table order which is crucial in scenarios where foreign key dependencies are present.

Exploring other avenues

It's all fun and games until the DROP TABLE command fails. Here are some alternative approaches and considerations when things don't go according to plan:

When in doubt, use system views

You might prefer sys.tables and sys.schemas over INFORMATION_SCHEMA.TABLES:

DECLARE @Sql NVARCHAR(MAX) = '';
SELECT @Sql += 'DROP TABLE [' + s.name + '].[' + t.name + '] '  FROM sys.tables t
INNER JOIN sys.schemas s ON t.schema_id = s.schema_id; -- When schemas and tables decide to tango!
EXEC sp_executesql @Sql; -- Feel free to dish out this punishment.

The magic of loops

Got complex dependencies? No problem! Here's a little magic trick using the WHILE loop with dynamic SQL:

DECLARE @TableName NVARCHAR(256)
WHILE EXISTS(SELECT * FROM sys.objects WHERE type in (N'U')) -- Becoming an existentialist!
BEGIN
    SELECT TOP 1 @TableName = '[' + schema_name(schema_id) + '].[' + name + ']' FROM sys.objects WHERE type in (N'U') ORDER BY create_date DESC
    EXEC ('DROP TABLE ' + @TableName) -- Table goes poof!
END

Go nuclear and recreate the database

Well, when life gives you a corrupted database, it's quicker to recreate it. However, with great power, comes great responsibility. Be prepared to lose all users and permissions settings.

The "Oh, no!" production moment

When you're handling a production environment, it's not just about careful—it's about being paranoid. Keep in mind the aftermath of dropping tables which includes data loss and downtime.

The mandatory pre-drop checks

Before you go on a drop-spree, conduct a due-check using INFORMATION_SCHEMA or sys.foreign_keys to understand the dependencies landscape. Anything worth keeping must be archived.

It's not just tables…

Consider other objects in your database. Stored procedures, views, and associated functions might have dependencies on your tables. With tables gone, these will need modifications or could turn invalid.

Proceed with caution

While the automated scripts are powerful, remember this is data we are dealing with. Avoid getting carried away. Double-check commands and be prepared for unexpected consequences.