Home / server / Questions / 7251
Accepted
Ryaner
Asked: 2009-05-13 00:14:23 +0800 CST

Microsoft SQL Server Management Studio 2008 deleting multiple user tables

  • 772

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.

sql-server sql-server-2008

4 Answers

  1. Best Answer

    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.

    • 26

  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.

    • 2

  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
    • 0

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

    • 0