Drop all tables whose names begin with a certain string

You may need to modify the query to include the owner if there's more than one in the database.

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

This is cleaner than using a two-step approach of generate script plus run. But one advantage of the script generation is that it gives you the chance to review the entirety of what's going to be run before it's actually run.

I know that if I were going to do this against a production database, I'd be as careful as possible.

Edit Code sample fixed.

SELECT 'DROP TABLE "' + TABLE_NAME + '"' 
FROM INFORMATION_SCHEMA.TABLES 
WHERE TABLE_NAME LIKE '[prefix]%'

This will generate a script.

Adding clause to check existence of table before deleting:

SELECT 'IF OBJECT_ID(''' +TABLE_NAME + ''') IS NOT NULL BEGIN DROP TABLE [' + TABLE_NAME + '] END;' 
FROM INFORMATION_SCHEMA.TABLES 
WHERE TABLE_NAME LIKE '[prefix]%'

CREATE PROCEDURE usp_GenerateDROP
    @Pattern AS varchar(255)
    ,@PrintQuery AS bit
    ,@ExecQuery AS bit
AS
BEGIN
    DECLARE @sql AS varchar(max)

    SELECT @sql = COALESCE(@sql, '') + 'DROP TABLE [' + TABLE_NAME + ']' + CHAR(13) + CHAR(10)
    FROM INFORMATION_SCHEMA.TABLES
    WHERE TABLE_NAME LIKE @Pattern

    IF @PrintQuery = 1 PRINT @sql
    IF @ExecQuery = 1 EXEC (@sql)
END

Xenph Yan's answer was far cleaner than mine but here is mine all the same.

DECLARE @startStr AS Varchar (20)
SET @startStr = 'tableName'

DECLARE @startStrLen AS int
SELECT @startStrLen = LEN(@startStr)

SELECT 'DROP TABLE ' + name FROM sysobjects
WHERE type = 'U' AND LEFT(name, @startStrLen) = @startStr

Just change tableName to the characters that you want to search with.

This worked for me.

DECLARE @sql NVARCHAR(MAX) = N'';

SELECT @sql += '
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 'something%';

PRINT @sql;
-- EXEC sp_executesql @sql;

I would like to post my proposal of the solution which DROP (not just generate and select a drop commands) all tables based on the wildcard (e.g. "table_20210114") older than particular amount of days.

DECLARE 
    @drop_command NVARCHAR(MAX) = '',
    @system_time date,
    @table_date nvarchar(8),
    @older_than int = 7
    
Set @system_time = (select getdate() - @older_than)
Set @table_date = (SELECT CONVERT(char(8), @system_time, 112))

SELECT @drop_command += N'DROP TABLE ' + QUOTENAME(SCHEMA_NAME(schema_id)) + '.' + QUOTENAME([Name]) + ';'
FROM <your_database_name>.sys.tables
WHERE [Name] LIKE 'table_%' AND RIGHT([Name],8) < @table_date

SELECT @drop_command
 
EXEC sp_executesql @drop_command

If your query returns more than one line, you can collect the results and merge them into a query.

declare @Tables as nvarchar(max) = '[schemaName].['
select @Tables =@Tables + TABLE_NAME +'],[schemaName].['
FROM INFORMATION_SCHEMA.TABLES 
WHERE TABLE_TYPE='BASE TABLE'
AND TABLE_SCHEMA = 'schemaName'
AND TABLE_NAME like '%whateverYourQueryIs%'

select @Tables =  Left(@Tables,LEN(@Tables)-13) --trying to remove last ",[schemaName].[" part, so you need to change this 13 with actual lenght 

--print @Tables

declare @Query as nvarchar(max) = 'Drop table ' +@Tables 

--print @Query


exec sp_executeSQL @Query

Try following code:

declare @TableLst table(TblNames nvarchar(500))
insert into @TableLst (TblNames)
SELECT 'DROP TABLE [' + Table_Name + ']'
FROM INFORMATION_SCHEMA.TABLES
WHERE Table_Name LIKE 'yourFilter%'
WHILE ((select COUNT(*) as CntTables from @TableLst) > 0)
BEGIN
    declare @ForExecCms nvarchar(500) = (select top(1) TblNames from @TableLst)
    EXEC(@ForExecCms)
    delete from @TableLst where TblNames = @ForExecCms
END

This SQL script is executed without using a cursor.