Take the 2-minute tour ×
Server Fault is a question and answer site for system and network administrators. It's 100% free, no registration required.

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.

share|improve this question

4 Answers 4

up vote 12 down vote accepted

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.

share|improve this answer
    
what about constraints? –  Simon Dec 13 '12 at 3:50

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.

share|improve this answer

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
share|improve this answer
    
This assumes no foreigh key constraints are in place. –  K. Brian Kelley May 12 '09 at 13:33
    
You can crawl dependencies and drop the tables in order with a bit more effort. The stackoverflow posting at <a href=stackoverflow.com/questions/352176/…; has some solutions for doing this. –  ConcernedOfTunbridgeWells May 12 '09 at 17:03

You can drop multiple tables by iterating through them and executing this:

EXEC sp_MSforeachtable @command1 = "DROP TABLE ?"

However, if you try to drop a table that's being referenced by a foreign key, you'll get an error like

Msg 3726, Level 16, State 1, Line 1
Could not drop object 'dbo.Table1' because it is referenced by a FOREIGN KEY constraint.

If you just want to do it manually then simply repeat the statement a few times untill the tables with the reference are dropped (e.g. if Table2 has a reference to Table1, then at first run Table1 cannot be dropped while Table2 is dropped, and at second run Table1 can then be dropped since Table2 is no more).

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.