In an ironic twist of fate I adapted my ‘Drop all temp-tables’ script, to drop all tables beginning with an underscore.
Yes, it is ironic, because it uses a temp-table to store the working list of tables to be deleted. Whilst my original script used a real table to store a list of temp-tables.
Well … ok then
-- DropAllTablesStartingWithAnUnderscore.sql IF OBJECT_ID('tempdb..#tables') IS NOT NULL DROP TABLE #tables SELECT [name] INTO #tables FROM sys.tables WHERE [name] LIKE '/_%' ESCAPE '/' DECLARE @table VARCHAR(200), @cmd VARCHAR(500) WHILE (SELECT COUNT(*) FROM #tables) > 0 BEGIN SET @table = (SELECT TOP(1) [name] FROM #tables) SET @cmd = 'drop table ' + @table EXEC(@cmd) DELETE FROM #tables WHERE [name] = @table END
(In case you were wondering why I created these underscore-tables in the first place. I was refactoring a stored-procedure that took over an hour to run, and had a large number of temp-tables. I wanted to persist those temp-tables for another day, and not have to start from scratch.)