Delete or Drop all tables whose names begin with a certain string

I would like a script to delet or 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 tap one up quickly, please post it.
If no-one posts an answer previous to I figure it out myself, I'll post my solution.

Solution :

You may require to modify the query to contain 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 create script plus run. But one advantage of the script creation 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 next to a production database, I'd be as careful as possible.

TAGS : | | |