How to delete all tables from db? Cannot delete from sys.tables

Question

How can I perform this query on whatever way:

delete from sys.tables where is_ms_shipped = 0

What happened is, I executed a very large query and I forgot to put USE directive on top of it, now I got a zillion tables on my master db, and don't want to delete them one by one.

UPDATE: It's a brand new database, so I don't have to care about any previous data, the final result I want to achieve is to reset the master db to factory shipping.

The `sys.*` objects are system catalog **views** which allow you to query - but not manipulate - views of the objects in your database. To CREATE or DROP objects, use those appropriate SQL commands. — marc_s, Jan 10 '11 at 06:01
Yes I realized that. I was looking for ANY way to get my changes out. — Shimmy Weitzhandler, Jan 12 '11 at 04:00

Phil Hunt · Answer 1 · 2011-01-10T01:42:23.137

7

If this is a one-time issue, use SQL Server Management Studio to delete the tables.

If you must run a script very, very carefully use this:

EXEC sp_msforeachtable 'DROP TABLE ?'

edited Jan 10 '11 at 01:42

answered Jan 10 '11 at 01:35

Phil Hunt

8,404
1
30
25

@Shimmy: See updated answer. This drops all user tables in the database, unless foreign keys need to first be dropped. It does not, however, remove users, synonyms, etc. – Phil Hunt Jan 10 '11 at 01:46
Also read the query in my question, I don't want to delete ALL the tables, I only want to delete tables that I accidentally added, not tables that's been shipped with the installation. – Shimmy Weitzhandler Jan 10 '11 at 01:48
@Shimmy: `sp_msforeachtable` processes for each *user* tables. It doesn't include system tables. – Phil Hunt Jan 10 '11 at 01:49
it doesn't work, there are relations and it doesn't delete them. – Shimmy Weitzhandler Jan 10 '11 at 01:53
2

@Shimmy - Can't you just run the script in a loop until there's no more tables then? `WHILE EXISTS(SELECT * FROM sys.tables where is_ms_shipped = 0) EXEC sp_MSforeachtable 'DROP TABLE ?'` – Martin Smith Jan 10 '11 at 02:53

score 3 · Answer 2 · answered Jan 10 '11 at 12:25

One method I've used in the past which is pretty simple and relatively foolproof is to query the system tables / info schema (depending on exact requirements) and have it output the list of commands I want to execute as the results set. Review that, copy & paste, run - quick & easy for a one-time job and because you're still manually hitting the button on the destructive bit, it's (IMHO) harder to trash stuff by mistake.

For example:

select 'drop table ' + name + ';', * from sys.tables where is_ms_shipped = 0

score 2 · Answer 3 · 2011-01-10T02:58:35.350

No backups? :-)

One approach may be to create a Database Project in Visual Studio with an initial Database Import. Then delete the tables and synchronize the project back to the database. You can do the deletes en masse with this approach while being "buffered" with a commit phase and UI.

I am fairly certain the above approach can be used to take care of the table relationships as well (although I have not tried in the "master" space). I would also recommend using a VS DB project (or other database management tool that allows schema comparing and synchronization) to make life easier in the future as well as allowing version-able (e.g. with SCM) schema change-tracking.

Oh, and whatever is done, please create a backup first. If nothing else, it is good training :-)

score 1 · Accepted Answer · answered Jan 10 '11 at 03:48

Simplest and shortest way I did was this:

How to Rebuild System Databases in SQL Server 2008

The problem with all other answers here is that it doesn't work, since there are related tables and it refuses to execute.

This one, not only it works but actually is what I am looking for: "Reset to factory defaults" as stated in the question.
Also this one will delete everything, not only tables.

score 1 · Answer 5 · edited Nov 01 '11 at 13:04

This code could be better but I was trying to be cautious as I wrote it. I think it is easy to follow an easy to tweak for testing before you commit to deleting your tables.

DECLARE 
    @Prefix VARCHAR(50),
    @TableName NVARCHAR(255),
    @SQLToFire NVARCHAR(350)

SET @Prefix = 'upgrade_%'

WHILE EXISTS(
    SELECT
        name
    FROM
        sys.tables
    WHERE
        name like @Prefix
    )
BEGIN
    SELECT
        TOP 1   --This query only iterates if you are dropping tables
        @TableName = name
    FROM
        sys.tables
    WHERE
        name like @Prefix

    SET @SQLToFire = 'DROP TABLE ' + @TableName

    EXEC sp_executesql @SQLToFire;
END

score 1 · Answer 6 · answered Dec 28 '11 at 15:16

I did something really similar, and what I wound up doing was using the Tasks--> script database to only script drops for all the database objects of the originally intended database. Meaning the database I was supposed to run the giant script on, which I did run it on. Be sure to include IF Exists in the advanced options, then run that script against the master and BAM, deletes everything that exists in the original target database that also exists in the master, leaving the differences, which should be the original master items.

score 0 · Answer 7 · answered Jan 10 '11 at 02:55

Not very elegant but as this is a one time task.

WHILE EXISTS(SELECT * FROM sys.tables where is_ms_shipped = 0) 
      EXEC sp_MSforeachtable 'DROP TABLE ?'

Works fine on this simple test (clearing a on the second loop after failing on the first attempt and proceeding onwards to delete b)

create table a
(
a int primary key
)
go

create table b
(
a int references a (a)
)

insert into a values (1)

insert into b values (1)

How to delete all tables from db? Cannot delete from sys.tables

7 Answers7