How I can delete all records from all tables of my database? Can I do it with one SQL command or I need for one SQL command per one table?
+13
A:
Usually I will just use the undocumented proc sp_MSForEachTable
-- disable referential integrity
EXEC sp_MSForEachTable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'
GO
EXEC sp_MSForEachTable 'TRUNCATE TABLE ?'
GO
-- enable referential integrity again
EXEC sp_MSForEachTable 'ALTER TABLE ? CHECK CONSTRAINT ALL'
GO
SQLMenace
2010-09-10 19:13:09
That's awesome.
Robert Harvey
2010-09-10 19:15:24
I don't think this works. Looks like Kalen Delaney was inadvertently responsible for starting this idea off. [Here she clarifies](http://www.eggheadcafe.com/software/aspnet/29927698/cant-truncate-table.aspx) "you have to drop the referencing constraint in order to truncate the table."
Martin Smith
2010-10-02 00:38:51
Martin I just ran it 2 seconds ago in the Adventureworks DB without a problem
SQLMenace
2010-10-02 00:45:18
It definitely does not work for me here. `create database testing; GO use testing; create table t1 (i int primary key) create table t2(i int primary key,p int references t1)`
Martin Smith
2010-10-02 01:19:35
+3
A:
It is usually much faster to script out all the objects in the database, and create an empty one, that to delete from or truncate tables.
AlexKuznetsov
2010-09-10 19:34:38