Drop Several Tables at Once

I have some tables that I need to drop on a regular basis. The names of the tables sometimes change but the table names always begin with ‘db_comp_temp’. Is it possible to write some SQL that will in effect do something like:

DROP TABLE db_comp_temp* 

Thanks in advance,

Answer

No, there is no wildcard support in DDL.

DECLARE @sql nvarchar(max) = N'';

SELECT @sql += N'DROP TABLE ' + QUOTENAME(s.name)
  + '.' + QUOTENAME(t.name) + ';
' FROM sys.tables AS t
INNER JOIN sys.schemas AS s
ON t.[schema_id] = s.[schema_id]
WHERE t.name LIKE N'db[_]comp[_]temp%';

PRINT @sql;
-- EXEC sys.sp_executesql @sql;

Or:

DECLARE @sql nvarchar(max) = N'';

SELECT @sql += N'
  ,' + QUOTENAME(s.name) + '.' + QUOTENAME(t.name)
FROM sys.tables AS t
INNER JOIN sys.schemas AS s
ON t.[schema_id] = s.[schema_id]
WHERE t.name LIKE N'db[_]comp[_]temp%';

SET @sql = N'DROP TABLE ' + STUFF(@sql, 1, 1, '');

PRINT @sql;
-- EXEC sys.sp_executesql @sql;

You could also do it with FOR XML PATH but I don’t think it’s necessary when you’re not grouping the result into another outer query.