Drop All Tables in a SQL Server Database


By:   |   Updated: 2021-04-02   |   Comments   |   Related: More > T-SQL


Smashing SQL Server Silos with DreamFactory

Free MSSQLTips Webinar: Smashing SQL Server Silos with DreamFactory

DreamFactory is an API management platform best known for its ability to generate REST APIs for a wide variety of data sources, including Microsoft SQL Server. Organizations around the globe use the platform to reduce if not entirely eliminate the time and overhead otherwise required to manually build MS SQL Server-backed APIs.


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.

  1. Expand Databases dropdown
  2. Expand database
  3. Expand Tables
  4. Right click on a table name
  5. Delete
Dropping tables in SSMS
  1. OK
Drop Table

Here we see the action failed.

  1. Click on the Message hyperlink
Drop Table Error

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
  1. Right click in the corner of the results window
  2. Copy
Generate SQL to Drop Constraints

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

DROP Constraints
-- generate sql to drop tables
SELECT 'DROP TABLE ' + '[' + TABLE_SCHEMA + '].[' + TABLE_NAME + ']'
FROM INFORMATION_SCHEMA.TABLES
ORDER BY TABLE_SCHEMA, TABLE_NAME
  1. Right click in the corner of the results window
  2. Copy
Generate SQL to Drop Tables

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

Drop 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
Dynamically Drop Tables in One Step

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.



Last Updated: 2021-04-02


get scripts

next tip button



About the author
MSSQLTips author Joe Gavin Joe Gavin is from Greater Boston. He has held many roles in IT and is currently a SQL Server Database Administrator.

View all my tips



Comments For This Article





download





Recommended Reading

SQL Server Cursor Example

Rolling up multiple rows into a single row and column for SQL Server data

SQL Server Loop through Table Rows without Cursor

Split Delimited String into Columns in SQL Server with PARSENAME

COALESCE SQL Function














get free sql tips
agree to terms