Drop multiple tables with string

Question

I have tables like lg-010-a..., lg-010-ac..., and so on, I have abc database,

I have a command window:

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

Will this drop all the tables starting with lg-010-?

Answer 1

Unfortunately you can't do it like that. One way is:

DECLARE @TableName NVARCHAR(128)
SELECT TOP 1 @TableName = TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME LIKE 'lg-010-%'
ORDER BY TABLE_NAME ASC

WHILE (@@ROWCOUNT > 0)
    BEGIN
        PRINT 'DROP TABLE [' + @TableName + ']'

        SELECT TOP 1 @TableName = TABLE_NAME
        FROM INFORMATION_SCHEMA.TABLES
            WHERE TABLE_NAME LIKE 'lg-010-%'
            AND TABLE_NAME > @TableName
        ORDER BY TABLE_NAME ASC 
    END

This will just print out the DROP TABLE statement for each table - you can then copy and paste this output and run it. You can just put an EXECUTE in the loop instead of the PRINT, but I've done it this way so you can see what's going on/check the output first.

Answer 2

Try something like this:

declare @sql varchar(max)
declare @tablenames varchar(max)

select @tablenames = coalesce(@tablenames + ', ','') + Table_Name from INFORMATION_SCHEMA.TABLES    
where Table_Name like ('lg-010-%')

set @sql = 'drop table ' + @tablenames

exec (@sql)

This queries the INFORMATION_SCHEMA.TABLES table to retrieve table names that match your criteria, then concatenates them together into a comma delimited string.

This string is than added to a 'Drop table ' statement and executed.

Drop table can take multiple comma delimited table names.

(I had originally had this query sys.tables but some research revealed that while they are currently equivalent, the Information_Schema method is quaranteed to work in future versions)