Delete all tables from a database at once

Asked

Viewed 19,790 times

8

Is there a way for me to erase an entire database at once? Isn’t it drop, is to delete only the records of all tables belonging to the database.

sql sql-server

  • using DELETE?

  • I hadn’t noticed it was for all the tables :P

  • delete I delete table by table and what I want is in a single command delete all at once, without pity.

2 answers

12


For all tables, run:

EXEC sp_MSForEachTable 'TRUNCATE TABLE ?'

Holds for SQL Server 2005 and higher. May fail with enabled constraints.

To delete everything with constraints, use:

-- desabilitar constraints
EXEC sp_msforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all"

-- excluir linhas de todas as tabelas
EXEC sp_MSForEachTable "DELETE FROM ?"

-- habilitar constraints
exec sp_msforeachtable "ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all"

It may also be necessary to arrange the columns Identity. For this, use:

EXEC sp_MSforeachtable "DBCC CHECKIDENT ( '?', RESEED, 0)"

I got it out of here (not to mention I didn’t mention ethics).

  • Morrison Mende, is giving violation error of Constraint(FK), but by his script he disables FK’s, right? This is the error: Cannot truncate table 'Dominios.Tbtipoprocesso' because it is being referenced by a FOREIGN KEY Constraint.

  • @pnet Use the second part of the script, please.

  • 1

    One more good answer :)

7

You’d have to create a script to do all the work. The simplest way to do this is to give a TRUNCATE in each of the tables. But this will not work well if you have certain constraints in the table. So you’d have to turn off all the restrictions before, which would be a certain amount of work.

So probably the easiest thing would be for you to copy the table structure to new tables without the data, possibly in another database, something like this:

SELECT * INTO tabela_nova From tabela_existente WHERE 1 = 2;

According to that response in the OS it is possible to automate the removal of restrictions and use the TRUNCATE thus:

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

I put in the Github for future reference.

Browser other questions tagged sql sql-server

You are not signed in. Login or sign up in order to post.