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.
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)
9 Comments. Leave new
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.
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.
That seems like one of those esoteric language things that no one uses, like numbered procs. proc;1 procl;2
Nice trick. Working on SQL for more than 3 year and not knowing this.
Thanks.
Your are right Pinal we dont know this option. Thank you
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
/*
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
good day ,sir g,
this query is not working on oracle 11g server,please tell me what should we do in this problem
It is not for Oracle, it is for SQL Server.