SQL SERVER – How to DROP Multiple Tables Using Single DROP Statement?

In my earlier post, SQL SERVER – How to DELETE Multiple Table Together Via SQL Server Management Studio (SSMS)? I showed you how to DROP Statement multiple tables together using a wizard in SQL Server Management Studio (SSMS).

Lots of developers are not aware that they can do it using single DROP statement.

SQL SERVER - How to DROP Multiple Tables Using Single DROP Statement? drop

Let us create the following three tables to demonstrate dropping multiple tables using a single DROP statement

USE TempDB
GO
CREATE TABLE testing1(ID INT, NAME VARCHAR(100));
CREATE TABLE testing2(ID INT, NAME VARCHAR(100));
CREATE TABLE testing3(ID INT, NAME VARCHAR(100));

Now to DROP all of these three tables, you do not need to use three DROP statements, All you need to do is simply use single DROP statement and specify the three table names separated by comma as shown below

DROP TABLE testing1,testing2,testing3;

That’s it. All three tables testing1,testing2, and testing3 are dropped

How many of you know this tip?

In your code, if you create lots of temporary tables and want to drop all of them at the end, you can make use of this simple tip.

If you have such a script, I request you to share the same with me. I will be happy to post the script with due credit to you over here.

Here are a few interesting blog posts:

Do Index Reorganization Update Statistics? – Interview Question of the Week #196

Reference: Pinal Dave (https://blog.SQLAuthority.com)

,
Previous Post
SQL SERVER – New Way to Use DBCC Page in SQL Server 2019
Next Post
SQL SERVER – How to Get List of SQL Server Instances Installed on a Machine Via T-SQL?

Related Posts

9 Comments. Leave new

  • Tim Cartwright
    October 24, 2018 2:38 am

    Wow, I did not know you can do that. Does it drop them in any particular order? The order listed maybe? Just thinking of FKs.

    Reply
    • As per documentation – Multiple tables can be dropped in any database. If a table being dropped references the primary key of another table that is also being dropped, the referencing table with the foreign key must be listed before the table holding the primary key that is being referenced.

      Reply
      • Tim Cartwright
        October 24, 2018 8:04 pm

        That seems like one of those esoteric language things that no one uses, like numbered procs. proc;1 procl;2

  • Shantilal Suthar
    October 24, 2018 12:02 pm

    Nice trick. Working on SQL for more than 3 year and not knowing this.

    Thanks.

    Reply
  • Your are right Pinal we dont know this option. Thank you

    Reply
  • halwer@gmx.de
    April 10, 2019 1:54 pm

    That’s cool. IF EXITS is also supported. Nice Feature.

    create table temp1(col1 NVARCHAR(1))
    –create table temp2(col1 NVARCHAR(1))
    create table temp3(col1 NVARCHAR(1))

    DROP TABLE IF EXISTS temp1,temp2,temp3

    Reply
  • Ahliana Byrd
    June 19, 2019 1:20 am

    /*
    I create multiple temporary stored procedures and temporary tables (NOT temp tables, I work in Azure).
    I’m essentially creating programming pieces for my overall stored procedure to use, such as logging or other
    specialized pieces. I prefix them all with _TEMP_, which makes them easy to search and destroy, or identify
    if they don’t get cleaned up.

    The following cursors clean them up.
    */

    GO
    DECLARE @Name NVARCHAR(MAX)
    DECLARE @SQL NVARCHAR(MAX)

    DECLARE curProcedures CURSOR FOR
    SELECT NAME
    FROM SYS.OBJECTS
    WHERE NAME LIKE ‘_TEMP_%’
    AND TYPE = ‘P’

    OPEN curProcedures
    FETCH NEXT FROM curProcedures INTO @Name
    WHILE @@FETCH_STATUS = 0
    BEGIN
    SET @SQL = ‘DROP PROCEDURE dbo.’ + QUOTENAME(@Name)
    EXEC sp_executesql @SQL
    FETCH NEXT FROM curProcedures INTO @Name
    END
    CLOSE curProcedures
    DEALLOCATE curProcedures
    GO

    GO
    DECLARE @Name NVARCHAR(MAX)
    DECLARE @SQL NVARCHAR(MAX)

    DECLARE curTables CURSOR FOR
    SELECT NAME
    FROM SYS.OBJECTS
    WHERE NAME LIKE ‘_TEMP_%’
    AND TYPE = ‘u’

    OPEN curTables
    FETCH NEXT FROM curTables INTO @Name
    WHILE @@FETCH_STATUS = 0
    BEGIN
    SET @SQL = ‘DROP TABLE dbo.’ + QUOTENAME(@Name)
    EXEC sp_executesql @SQL
    FETCH NEXT FROM curTables INTO @Name
    END
    CLOSE curTables
    DEALLOCATE curTables
    GO

    Reply
  • good day ,sir g,
    this query is not working on oracle 11g server,please tell me what should we do in this problem

    Reply

Leave a Reply

Menu