12

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.

26

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.

  • what about constraints? – Simon Dec 13 '12 at 3:50
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.

0

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
0

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).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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