Drop All Tables in SQL Server and Generate a List of Objects to Drop

By: Aubrey Love   |   Updated: 2022-10-19   |   Comments   |   Related: 1 | 2 | 3 | 4 | 5 | 6 | > Database Design

Problem

I have created five tables, 15 views, and four stored procedures in my Microsoft SQL Server test environment. At this point, I have completed my testing and moved everything to our production environment. Now, I need to delete all the SQL Server objects in my test environment to prepare for the next project.

I know I can create several SQL Server scripts (DROP TABLE, DROP VIEW and DROP PROC), but I would need to do so for each of the 24 objects. How can I drop all these objects more efficiently?

Solution

In this SQL tutorial, we will discuss a simple shortcut to drop all 24 SQL Server objects in only three SQL statements. Usually, you would have to write a SQL DROP statement for each object; however, you can also drop all your tables in one SQL statement, all your views in another SQL statement, and all your stored procedures in another.

SQL DROP Table for all Tables in a SQL Server Database

The solution is straightforward - list all tables in your SQL Server database, for example, in one DROP statement separated by commas.

Example syntax:

DROP TABLE table1, table2, table3, table4, table5;

In our scenario, we will create five tables, populate them with some generic data, and then drop all five tables with a single SQL command.

For simplicity's sake, all five tables will be identical but have different names. First, let's create the primary table and populate it with some generic data.

CREATE TABLE Students1(
     colID INT
   , name VARCHAR(20)
   , subject VARCHAR(20)
   );
GO
INSERT INTO Students1(name, subject)
VALUES ('Student1', 'Science')
     , ('Student2', 'Science')
     , ('Student3', 'History');
GO

Let's create four more tables using a "table copy" process.

SELECT *
INTO Students2
FROM Students1;
GO
 
SELECT *
INTO Students3
FROM Students2;
GO
 
SELECT *
INTO Students4
FROM Students3;
GO
 
SELECT *
INTO Students5
FROM Students4;
GO

Drop All Tables with One SQL Query

You can drop all the tables at once with this single SQL query.

DROP TABLE
Students1,
Students2,
Students3,
Students4,
Students5;
GO

Advantages and Disadvantages

Like with many things in SQL and life, there are often some advantages and disadvantages. The same is true with using bulk drop statements. It's up to you to determine, in each scenario, whether the benefits make it worth your while to use this option. Below is a quick reference listing the advantages and disadvantages of this option.

Advantages

• The bulk drop option is compatible with all versions of SQL Server, including Azure.
• You can save time by writing less code.
• If one of the objects in the list does not exist or can't be dropped due to a dependency, it does not affect the remainder of the DROP statement. All other objects in the list will be deleted.

Let's see that last bullet item in action.

Here's a screenshot of our tables currently in our test database. If you have already dropped them, rerun the CREATE TABLE scripts.

Now, let's run the bulk drop table statement for all our tables but mislabel one of the tables to simulate a table that doesn't exist or has a dependency. Here, we will change the name of the Students3 table to Students33 and run the script.

DROP TABLE
Students1,
Students2,
Students33,
Students4,
Students5;
GO

You should receive an error message like the one below.

Notice it didn't say anything about the other tables, only the table "Students33", which does not exist. So, when we refresh our tables in the Object Explorer, all tables except "Students3" should be gone.

Disadvantages

• You cannot use IF EXISTS in the bulk drop statement.
• You cannot drop more than one object type at a time. In other words, you must drop all tables in one command, all views in another, and so on.

Finding the SQL Object Names

So, you can drop all tables, views, procedures, etc., with a single command. Do you still need to type in all those table names manually? How will this save time, especially if some objects have long names?

Let's address this issue using the sample tables created earlier. We can use some tools built into SQL Server Management Studio (SSMS): 1) "sys.objects" and 2) the ability to return the query results as a text file.

First, we will create a SQL script to list our table names. You can also list your views, stored procedures, etc. using "sys.objects".

SELECT *
FROM sys.objects;
GO

Results (partial list):

However, we usually don't need that much information, but it's a good idea to run the above script to get familiar with what values are returned. You can restrict this SQL command to return only the name and type of objects in your database:

select name + ', ', TYPE
from sys.objects

Results (partial list):

Now, let's refine that further to omit all the system-based objects.

sselect name + ', ', TYPE
from sys.objects
where type_desc != 'SYSTEM_TABLE'
AND type != 'IT'
AND type != 'SQ'

Results:

As you can see, we have three different "TYPEs" listed: U, V, and P.

• U represents a database TABLE,
• V represents a database VIEW,
• and P represents a database PROCEDURE, such as a stored procedure.

Finally, let's return all the tables I created today during the testing. You can also set this as a date range. Before you run the following script, switch your result set to return as a text file. You can do this by clicking the "Results to Text" button on the toolbar or pressing CTRL + T on the keyboard.

select name + ', '
from sys.objects
where type = 'U'
AND create_date >= '2022-09-28';
GO

Results:

Finally, you can copy and paste the results into your DROP TABLE SQL command.

Next Steps

• SQL Server DROP TABLE IF EXISTS Examples
• SQL Server System Objects in User Defined Databases

About the author

Aubrey Love is a self-taught DBA with more than six years of experience designing, creating, and monitoring SQL Server databases as a DBA/Business Intelligence Specialist. Certificates include MCSA, A+, Linux+, and Google Map Tools with 40+ years in the computer industry. Aubrey first started working on PC’s when they were introduced to the public in the late 70's.

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

View all my tips