Drop all tables whose names begin with a certain string

Question

I'd like a script to drop all tables whose name begins with a given string. I'm sure this can be done with some dynamic SQL and the INFORMATION_SCHEMA tables.

If anyone has a script or can knock one up quickly, please post it.

If no-one posts an answer before I figure it out myself, I'll post my solution.

Answer 1

You should modify the query to include the owner if there's more than one in the database like this.

Query:

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

But the advantage of the script generation is that it will give you the chance to review the entirety of what's going to be run before it'll actually run.