Drop All Tables SQL Server

By: Joe Gavin | Updated: 2021-04-02 | Comments (4) | Related: 1 | 2 | 3 | 4 | 5 | 6 | > Database Design

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.

Drop Tables from SQL Server Management Studio (SSMS)
Dynamically Generate SQL to Drop Constraints and Tables Programmatically
Dynamically Generate and Execute SQL to Drop Constraints and Tables Programmatically

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

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

TSQL 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 }

Database

We’ll be using the BikeStores sample database for our examples.

Drop Tables from SQL Server Management Studio (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.

Dynamically Generate SQL to Drop Constraints and Tables Programmatically

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.

warning 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.

Dynamically Generate and Execute SQL to Drop Constraints and Tables Programmatically

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.

warning 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.

Next Steps

Following are some links to more information.

About the author

Joe Gavin is from the Greater Boston area. He started working with SQL Server and Sybase in 1998 in the financial services industry and has been a SQL Server Database Administrator for a dairy cooperative since 2011. He graduated from Northeastern University in Boston with a Bachelor of Science in Engineering Technology (BSET) degree in Computer Technology. Joe has spoken at the Boston and Providence SQL Saturday events.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Article Last Updated: 2021-04-02

Drop All Tables in a SQL Server Database