TL;DR — Quick Summary

Delete all tables from a SQL Server database using sp_MSforeachtable or sys.tables loop. Quick script to drop all tables in a database at once.

Note: This article was originally published in 2011. Some steps, commands, or software versions may have changed. Check the current Microsoft documentation for the latest information.

How to delete all tables from a SQL Server database

During data migrations, development testing, or staging deployments, you often find yourself needing to completely wipe a database and start over. While you can right-click and delete tables one by one in SQL Server Management Studio (SSMS), this is incredibly tedious.

Instead of manually deleting or dropping the entire database itself (which would require you to reconfigure user permissions and file settings), you can use a hidden, undocumented stored procedure in SQL Server to iterate through and drop every table automatically.

CRITICAL WARNING: Use these scripts extremely carefully. There is no “Undo” or Rollback for a DROP TABLE command outside of restoring from a backup. Ensure you are connected to the correct database before executing.

The Quick Method (No Foreign Keys)

If your database contains simple tables without any Foreign Key (FK) constraints, you can use the undocumented sp_MSforeachtable system stored procedure. This procedure loops through every user table in the current database and executes a command you provide.

Run the following script in a New Query window:

-- Drops every table in the database
EXEC sp_MSforeachtable @command1 = "DROP TABLE ?"

(You can also run this via command line: sqlcmd -U your_user -P your_password -Q "EXEC sp_MSforeachtable @command1 = 'DROP TABLE ?'")

The Robust Method: Handling Foreign Key Constraints

The quick method above frequently fails in real-world databases. When you attempt to drop a table that is referenced by a Foreign Key in another table, SQL Server will throw an error and abort the drop.

To successfully drop all tables in a relational database, you must drop all Foreign Key constraints first, and then drop the tables.

Here is the complete script to perfectly wipe all tables and their constraints:

-- 1. First, declare variables to hold the constraint drop commands
DECLARE @SqlStatement NVARCHAR(MAX)
DECLARE @Cursor CURSOR

-- 2. Build a cursor that finds every Foreign Key constraint in the database
SET @Cursor = CURSOR FAST_FORWARD FOR
SELECT 
    'ALTER TABLE [' + OBJECT_SCHEMA_NAME(parent_object_id) + '].[' + OBJECT_NAME(parent_object_id) + '] ' +
    'DROP CONSTRAINT [' + name + '];'
FROM sys.foreign_keys;

-- 3. Loop through and execute the DROP CONSTRAINT commands
OPEN @Cursor
FETCH NEXT FROM @Cursor INTO @SqlStatement

WHILE (@@FETCH_STATUS = 0)
BEGIN
    EXEC sp_executesql @SqlStatement
    FETCH NEXT FROM @Cursor INTO @SqlStatement
END

CLOSE @Cursor
DEALLOCATE @Cursor

-- 4. Now that constraints are gone, drop all the tables safely
EXEC sp_MSforeachtable @command1 = "DROP TABLE ?"

PRINT 'All tables have been successfully dropped.'

Why use sp_MSforeachtable?

sp_MSforeachtable replaces the ? character with the schema and name of each table (e.g., [dbo].[Users]). It abstracts away the need for you to write your own cursors to loop over sys.tables when performing bulk operations, saving significant time.