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

Question

How can I execute this query in any way:

delete from sys.tables where is_ms_shipped = 0 

What happened, I fulfilled a very large request, and I forgot to put the USE directive on top of it, now I have a zillion table on my master db and do not want to delete them one by one.

UPDATE: this is a completely new database, so I don’t need to care about any previous data, the end result I want to achieve is reset from master db to factory.

Answer 1

The easiest and shortest way I've done is:

How to rebuild system databases in SQL Server 2008

The problem with all the other answers here is that it does not work because there are linked tables and it refuses to execute.

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

Answer 2

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

If you have to run the script very, very , use this:

 EXEC sp_msforeachtable 'DROP TABLE ?' 

Answer 3

One of the methods that I used in the past, quite simple and relatively reliable, is to query the system tables / information schema (depending on the exact requirements) and list the commands that I want to execute as a result set.View this, copy and paste, run - quick and easy for a one-time operation and because you still manually press the button on the destructive bit, this (IMHO) is more difficult to garbage by mistake.

For example:

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

Answer 4

No backups ?:-)

One approach might be to create a database project in Visual Studio with the initial import of the database. Then delete the tables and synchronize the project with the database. You can do bulk deletion using this approach, being “buffered” with a commit phase and user interface.

I am quite sure that the above approach can be used to care for relationships in the table (although I have not tried it in the "master space"). I would also recommend using the VS DB project (or another database management tool that allows you to compare and synchronize the schema) to make life easier in the future, as well as tracking changes to the schema that is compatible with versions (for example, with SCM).

Oh, and all that is done, first back up. If nothing else, this is a good preparation :-)

Answer 5

Not very elegant, but since it is a one-time task.

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

Works great in this simple test (cleaning a in the second cycle after a failure on the first attempt and subsequent deletion to remove 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) 

Answer 6

This code might be better, but I tried to be careful when I wrote. I think it’s easy to do an easy setup for testing before you decide to delete 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 

Answer 7

I did something really similar, and what I did was use the Tasks → script database for script drops only for all database objects of the originally intended database. The value of the database in which I had to run the giant script on which I ran it. Be sure to include IF Exists in the advanced parameters, then run the script against the master and BAM, delete everything that exists in the original target database, which also exists in the main database, leaving the differences that should be the original main elements.