Dropping Multiple Tables That Include A Specific String In Their Names

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. :-)