Delete all the data from all tables

Question

I have a little problem. I have to delete all the data from our database. I don't want to drop tables, only delete the data sets.

These are 47 tables in total.

I tried the following:

Delete FROM(select TABLE_NAME from USER_TABLES);
Delete FROM(select * from ALL_ALL_TABLES Where OWNER = 'DB_NAME')

But that is not valid. How can I efficiently remove all the data from all my tables?

Answer 1

Generate the statements you need

select 'DELETE FROM '||table_name||' ;' from user_tables;

Even better would be using TRUNCATE instead of DELETE. That would prevent generating extra redo vectors in the redo log - in the end all you want to do is just get rid of the data. It would also avoid all actions by any DELETE triggers that the tables have.

select 'TRUNCATE TABLE '||table_name||' ;' from user_tables;

Answer 2

You should use TRUNCATE instead of DELETE if you is using SQL Server. Because it will reduce log records in log file.

USE <your database>;
GO

DECLARE @table_name varchar(100);

DECLARE c CURSOR FOR SELECT name FROM sys.tables WHERE type='U' 
OPEN c
FETCH NEXT FROM c INTO @table_name
WHILE @@FETCH_STATUS =0
BEGIN
    EXEC (N'TRUNCATE TABLE dbo.'+@table_name);
    FETCH NEXT FROM c INTO @table_name
END
CLOSE c
DEALLOCATE c

Answer 3

TRUNCATE TABLE_NAME; does the magic for you.