Drop All Tables in SQL Server Database

Problem

You need to drop all of the user tables in a database. It’s probably easiest to just drop and recreate the database but it’s possible you have rights in the database but don’t have rights to drop and recreate it.

Solution

We’ll look at three ways to drop all tables in a database:

Option 1 – Drop Tables from SQL Server Management Studio (SSMS)
Option 2 – Dynamically Generate SQL to Drop Constraints and Tables Programmatically
Option 3 – Dynamically Generate and Execute SQL to Drop Constraints and Tables Programmatically

DROP TABLE and ALTER TABLE Syntax

DROP TABLE { database_name.schema_name.table_name | schema_name.table_name | table_name } [ ,...n ] [ ; ] 
ALTER TABLE { database_name.schema_name.table_name | schema_name.table_name | table_name }

Permissions Required

ALTER TABLE and DROP TABLE require the user to have one of the following:

ALTER permission on the table’s schema
CONTROL permission on the table
Membership in the db_ddladmin fixed database role

Option 1 – Drop Tables from SSMS

Open SQL Server Management Studio Object Explorer.

Expand Databases dropdown
Expand database
Expand Tables
Right click on a table name
Delete

Here we see the action failed.

Click on the Message hyperlink

The error message “Could not drop object ‘production.brands’ because it is referenced by a FOREIGN KEY constraint. (Microsoft SQL Server, Error: 3726)” is telling us why the table cannot be dropped. In this case we would need to step through dropping the constraints then dropping the tables or step through dropping each child table and coming back to drop each parent table. It’s clear this would very quickly become be tedious. This method would be the easiest if we only had a few tables and no constraints.

Option 2 – Dynamically Generate SQL Code to Drop Constraints and Tables

Is there a better way? Can we dynamically generate a script to drop the constraints and tables? Yes, we can. This example will show how to generate the scripts then copy and paste them into a query window to execute.

CAUTION: Be sure you are in the correct database that you want to drop the tables in and check the generated script to be sure before you execute it. It would be very easy to accidentally run this somewhere you do not want to.

-- use database
USE [BikeStores]
GO
  
-- generate sql to drop constraints
SELECT 'ALTER TABLE ' 
    + (OBJECT_SCHEMA_NAME(parent_object_id)) 
    + '.'  
    +  QUOTENAME(OBJECT_NAME(parent_object_id)) 
    + ' ' 
    + 'DROP CONSTRAINT' 
    + QUOTENAME(name)
FROM sys.foreign_keys
ORDER BY OBJECT_SCHEMA_NAME(parent_object_id), OBJECT_NAME(parent_object_id);
GO

Right click in the corner of the results window
Copy

Paste the clipboard into another query window and execute to drop all the constraints.

-- generate sql to drop tables
SELECT 'DROP TABLE ' + '[' + TABLE_SCHEMA + '].[' + TABLE_NAME + ']'
FROM INFORMATION_SCHEMA.TABLES
ORDER BY TABLE_SCHEMA, TABLE_NAME

Right click in the corner of the results window
Copy

Paste the clipboard into another query window and execute to drop all the tables.

Here is the T-SQL to generate both scripts at the same time.

-- use database
USE [BikeStores]
GO
  
-- generate sql to drop constraints
SELECT 'ALTER TABLE ' 
     + (OBJECT_SCHEMA_NAME(parent_object_id)) 
    + '.'  
    +  QUOTENAME(OBJECT_NAME(parent_object_id)) 
    + ' ' 
    + 'DROP CONSTRAINT' 
    + QUOTENAME(name)
FROM sys.foreign_keys
ORDER BY OBJECT_SCHEMA_NAME(parent_object_id), OBJECT_NAME(parent_object_id);
GO
  
-- generate sql to drop tables
SELECT 'DROP TABLE ' + '[' + TABLE_SCHEMA + '].[' + TABLE_NAME + ']'
FROM INFORMATION_SCHEMA.TABLES
ORDER BY TABLE_SCHEMA, TABLE_NAME

This method is best if there are constraints and more than just a few tables. It’s safe as you have the dynamically generated script to inspect before you run it.

Option 3 – Dynamically Generate and Execute SQL to Drop Constraints and Tables

Can we generate and also run the scripts in if we want to eliminate the cumbersome copying and pasting? Yes, we can do it dynamically by creating a variable large enough to hold the generated SQL, populate the variable with the TABLE ALTER in one batch and the DROP TABLE in another, then execute the SQL held in the variables with sp_executesql.

CAUTION: Be extra careful here that you’re in the correct database as you will not have the opportunity to check the generated script output before it’s executed.

-- use database
USE [BikeStores]
GO
  
-- drop constraints
DECLARE @DropConstraints NVARCHAR(max) = ''
SELECT @DropConstraints += 'ALTER TABLE ' + QUOTENAME(OBJECT_SCHEMA_NAME(parent_object_id)) + '.'
                        +  QUOTENAME(OBJECT_NAME(parent_object_id)) + ' ' + 'DROP CONSTRAINT' + QUOTENAME(name)
FROM sys.foreign_keys
EXECUTE sp_executesql @DropConstraints;
GO

Dynamically Drop Constraints in One Step

Next, we’ll do something similar to drop the tables now that there are no more constraints.

-- use database 
USE [BikeStores]
GO
  
-- drop tables
DECLARE @DropTables NVARCHAR(max) = ''
SELECT @DropTables += 'DROP TABLE ' + QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)
FROM INFORMATION_SCHEMA.TABLES
EXECUTE sp_executesql @DropTables;
GO

And here is the complete script to generate and execute both drop scripts together.

-- use database
USE [BikeStores]
GO
  
-- drop constraints
DECLARE @DropConstraints NVARCHAR(max) = ''
SELECT @DropConstraints += 'ALTER TABLE ' + QUOTENAME(OBJECT_SCHEMA_NAME(parent_object_id)) + '.'
                        +  QUOTENAME(OBJECT_NAME(parent_object_id)) + ' ' + 'DROP CONSTRAINT' + QUOTENAME(name)
FROM sys.foreign_keys
EXECUTE sp_executesql @DropConstraints;
GO
  
-- drop tables
DECLARE @DropTables NVARCHAR(max) = ''
SELECT @DropTables += 'DROP TABLE ' + QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)
FROM INFORMATION_SCHEMA.TABLES
EXECUTE sp_executesql @DropTables;
GO

This method is riskier due to the script automatically being executed but is best if you need to run it repeatedly.

Versions Used in this Tip

Windows Server 2019 Standard 10.0 <X64>
SQL Server 2019 (RTM-CU8) Developer Edition (64-bit)
SQL Server Management Studio 18.8

Key Takeaways

You can drop all tables in a SQL database through three methods: using SQL Server Management Studio (SSMS), dynamically generating SQL code, or executing SQL directly.
Option 1 involves manually deleting tables in SSMS, but this can be tedious with constraints in place.
Option 2 allows you to generate a script to drop constraints and tables programmatically, which is safer and more efficient.
Option 3 automatically generates and executes scripts, but you must be careful since it lacks a review step before execution.
Ensure you have sufficient permissions to drop tables, like ALTER permission or membership in the db_ddladmin role.

Next Steps

Following are some links to more information.

Joe Gavin

Joe Gavin is from Greater Boston and has worked in technology as a Field Service Engineer for an automotive dealer computer system vendor, a Technical Consultant and Operations Analyst with Sybase and SQL Server based database applications in financial services and now works as a SQL Server Database Administrator.

MSSQLTips Awards:
Achiever Award (75+ Tips) – 2024 | Author of the Year – 2021 | Author Contender – 2024 |
Rookie Contender – 2018

4 Comments

Joe Gavin
April 11, 2022 / 9:51 am Reply
Thank you Sabhajeet.
Sabhajeet Kumar
April 10, 2022 / 7:47 am Reply
I really appreciate your efforts and I always learn from this website. And after much google I found solution only here even couldn’t find on Stackoverflow.com but Thank you sir for your page I got super solution https://www.mssqltips.com/sqlservertip/6798/drop-all-tables-sql-server/
Joe Gavin
April 9, 2022 / 5:47 am Reply
Thanks Tom.
Tom Wickerath
April 2, 2022 / 3:03 pm Reply
In the Problem section, you can add a note: Also very useful if you screw up by accidently adding a lot of tables to the Master database! :-)
For your table scripting, I had to add a WHERE clause as it picks up Views with the DROP TABLE statement (which fail, as expected):
— Generate SQL to drop all tables
select ‘drop table ‘ + ‘[‘ + table_schema + ‘].[‘ + table_name + ‘];’
from information_schema.tables
where table_type = ‘base table’
order by table_schema, table_name;

Drop All Tables SQL Server