How to drop/delete/truncate all tables in a specific database?

First we need a test database and test tables


--- Create a test db
create database DUMMYDB;
go
---- create tables in test db
use DUMMYDB;
go
create table t1(id int);
go
create table t2(id int);
go
create table t3(id int);
go

if there is no foreign keys, you can execute one of follow queries (drop, delete or truncate)


use DUMMYDB;
go
exec sp_MSforeachtable "DROP TABLE ? PRINT '? dropped' " --- drop all tables in current db
exec sp_MSforeachtable "DELETE FROM ? PRINT '? deleted' " --- delete all tables in current db
exec sp_MSforeachtable "TRUNCATE TABLE ? PRINT '? truncated' " --- truncate all tables in current db

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.