Delete all tables from a bank at once

For all tables, run:

EXEC sp_MSForEachTable 'TRUNCATE TABLE ?'

Worth it for SQL Server 2005 and above. 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 clean up the identity columns. To do this, use:

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

I got it out of here (not to say I did not talk about ethics).

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

So probably the easiest would be to copy the structure of the tables 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 this answer in OS you can automate the removal of constraints and use TRUNCATE like this: / p>

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