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

Shimmy Weitzhandler picture Shimmy Weitzhandler · Jan 10, 2011 · Viewed 19k times · Source

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

Phil Hunt picture Phil Hunt · Jan 10, 2011

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