Delete all the data from all tables

2018-02-02 21:25:24

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?

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_name||' ;' from user_tables;

We can do this task by either using cursors or loops but here is a simple way too.

EXEC sp_MSForEachTable 'DISABLE TRIGGER ALL ON ?'

GO

EXEC sp_MSForEachTable 'ALT

  • 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_name||' ;' from user_tables;

    2018-02-02 21:44:53
  • We can do this task by either using cursors or loops but here is a simple way too.

    EXEC sp_MSForEachTable 'DISABLE TRIGGER ALL ON ?'

    GO

    EXEC sp_MSForEachTable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'

    GO

    EXEC sp_MSForEachTable 'DELETE FROM ?'

    GO

    EXEC sp_MSForEachTable 'ALTER TABLE ? CHECK CONSTRAINT ALL'

    GO

    EXEC sp_MSForEachTable 'ENABLE TRIGGER ALL ON ?'

    GO

    2018-02-02 21:55:42
  • You should use TRUNCATE instead of DELETE if you is using SQL Server. Because it will reduce log records in log file.

    USE ;

    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

    2018-02-02 22:32:21
  • TRUNCATE TABLE_NAME;

    does the magic for you.

    2018-02-02 22:36:22