Few days ago, I was working on huge database and I have to remove 80+ database tables with prefix “Temp_” (tables were created during data migration process).
After few minutes of working, I came across the best solution. I found a query which drops database tables with given prefix by querying information schema table.
I used following Sql Query to delete Tables with prefix “Temp_“.
DECLARE @cmd varchar(4000)
DECLARE cmds CURSOR FOR
SELECT 'drop table [' + Table_Name + ']'
WHERE Table_Name LIKE 'PrefixHere%'
WHILE 1 = 1
FETCH cmds INTO @cmd
IF @@fetch_status != 0 BREAK
Replace PrefixHere with your table prefix.
P.S.: Don’t remove % sign after PrefixHere text.