views:

177

answers:

1

Possible Duplicate:
How to empty a SQL database?

What is the fastest way to delete all records from all tables in the db supposing they do not have too much data(may be a few records in some tables but no more)?

I believe that recreate the database from structure dump is much longer variant.

A: 

TRUNCATE TABLE name1, name2, ... CASCADE

TRUNCATE quickly removes all rows from a set of tables. It has the same effect as an unqualified DELETE on each table, but since it does not actually scan the tables it is faster. This is most useful on large tables.

If you have a lot of tables you might want to query for the list of tables and construct the truncate query dynamically, but for a small number of tables you could just type the names in by hand.

See this post for information on selecting all tables and running a query dynamically. The example given is for grantall but the idea is the same.

Mark Byers