Good day everyone!
It is I, Geon Bell, back with another SQL Something! (Still alive!)
Today we are going to look at something questionably dangerous:
Dropping Multiple Tables at Once Via One Query!
Yeah, I'm shocked at myself for even suggesting that.
Not gonna lie, I still get very... anxious when performing table drops, much less doing them en masse, but it'll be fiiiine. Maybe.
On a serious note, my scenario was as follows: I had come across a situation where I restored a Azure SQL bacpac on my local machine, in order to fiddle around with it safely. The database, however, was part of an Azure DataSync and came with a bunch of tables I didn't need. Thankfully they all ended with the same suffix.
I didn't want to drop each table individually, so I went looking across the internet for an easier way and found this link:
https://stackoverflow.com/questions/4393/drop-all-tables-whose-names-begin-with-a-certain-string
Thank you very much to
Curt and
Filip!
Now the query they gave was as follows:
DECLARE @cmd varchar(4000)
DECLARE cmds CURSOR FOR
SELECT 'drop table [' + Table_Name + ']'
FROM INFORMATION_SCHEMA.TABLES
WHERE Table_Name LIKE 'prefix%'
OPEN cmds
WHILE 1 = 1
BEGIN
FETCH cmds INTO @cmd
IF @@fetch_status != 0 BREAK
EXEC(@cmd)
END
CLOSE cmds;
DEALLOCATE cmds
When I ran it (with my appropriate suffix), I got the error: "Cannot drop table MyTableName because it does not exist or you do not have permission." for each table.
Checking the online again, I saw that people removed the '[' brackets from their regular drop statements to solve that issue. It still didn't work, so I also added the schema name as well.
I then combined the three solutions: The original query, AND I removed the squared brackets AND added the schema name. The final working solution for me was as follows:
DECLARE @cmd varchar(4000)
DECLARE cmds CURSOR FOR
SELECT 'drop table SchemaName.' + Table_Name --Add schema name, remove brackets
FROM INFORMATION_SCHEMA.TABLES
WHERE Table_Name LIKE 'prefix%'
OPEN cmds
WHILE 1 = 1
BEGIN
FETCH cmds INTO @cmd
IF @@fetch_status != 0 BREAK
EXEC(@cmd)
END
CLOSE cmds;
DEALLOCATE cmds
And that's that. :-)
DISCLAIMER: As stated, I’m not an expert so please, PLEASE feel free to politely correct or comment as you see fit. Your feedback is always welcomed. :-)
