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.

Answer 1

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 ?'

Answer 2

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