MS-SQL: 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

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

This will generate a script...

Answer 2

Xenph Yan's answe 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.

Answer 3

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 local
deallocate local

EDIT: 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.

Answer 4

Thanks Curt, that's the same sort of solution that I was midway through myself.

Yours is nicer than mine though - it lends itself to easy modification. I added a union to the select and wiped out some views as well ;)

declare @cmd varchar(4000)
declare cmds cursor for 
Select 'drop table [' + Table_Name + ']'
From    INFORMATION_SCHEMA.TABLES
Where   Table_Name like 'prefix%'
union
Select 'drop view [' + Table_Name + ']'
From    INFORMATION_SCHEMA.VIEWS
Where   Table_Name like 'prefix%'
open cmds
while 1=1
begin
    fetch cmds into @cmd
    if @@fetch_status != 0 break
    exec(@cmd)
end
close local
deallocate local

Don't worry, it's not a production database - this is just for easy clean-up of my dev db while I try stuff out.

Answer 5

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 PRINT @sql
    IF @ExecQuery EXEC (@sql)
END

Answer 6

hello,

i have tables like lg-010-a..., lg-010-ac..., and so, i have abc database, i have a command window

drop table from abc where Table_Name like 'lg-010-%'

this will drop all the tables starting with lg-010-

best regards