Microsoft SQL Server Management Studio 2008 deleting multiple user tables

Question

Is there an easy way to delete multiple tables in the database without dropping the database and recreating it? In this case we have over 100 to remove.

I am happy enough to remove all user tables and reimport the needed data but can't touch any of the database security settings.

Answer 1

In object explorer, navigate to the database you're interested in. Expand it out and click on the Tables folder. Hit F7 to bring up the Object Explorer Details. Select the tables you want to delete and press the delete key.

Answer 2

Any reason not to do it directly in T-SQL (with DROP TABLE)? Then it's just a case of creating the appropriate SQL script (quite possibly autogenerating it if you've got a list of the tables you need to delete) and you're away.

Answer 3

Tsql answer as suggested. I couldn't get the drop table to work in tsql but this did the trick.

declare @TABLE varchar(250)

declare select_cursor cursor for
select name from sysobjects where type='U'

open select_cursor

fetch next from select_cursor
into @TABLE

while @@FETCH_STATUS = 0
begin
    print 'DROP TABLE '+@TABLE

    fetch next from select_cursor
    into @TABLE
end

close select_cursor
deallocate select_cursor