0

How can I truncate all tables of a database?

thaddeusmt
  • 15,410
  • 9
  • 67
  • 67
ramezani.saleh
  • 571
  • 2
  • 8
  • 17

4 Answers4

2

I use the script

EXEC sp_MSForEachTable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'
EXEC sp_MSForEachTable 'DELETE FROM ?'
EXEC sp_MSForEachTable 'ALTER TABLE ? CHECK CONSTRAINT ALL'
GO
Tom van Enckevort
  • 4,170
  • 1
  • 25
  • 40
Boycs
  • 5,610
  • 2
  • 28
  • 23
2

You can see this post : how-do-you-truncate-all-tables-in-a-database-using-tsql

Community
  • 1
  • 1
Michaël
  • 6,676
  • 3
  • 36
  • 55
  • **I got this error:** **Cannot truncate table 'dbo.TBL#EstenadeGhanooni' because it is being referenced by a FOREIGN KEY constraint.** **after execute this code:** `EXEC sp_MSForEachTable` 'TRUNCATE TABLE ?' -- disable all constraints `EXEC sp_msforeachtable` "ALTER TABLE ? NOCHECK CONSTRAINT all" -- delete data in all tables `EXEC sp_MSForEachTable` "DELETE FROM ?" -- enable all constraints `EXEC sp_msforeachtable` "ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all" -- EXEC sp_MSforeachtable "DBCC CHECKIDENT ( '?', RESEED, 0)" **What is the problem?** – ramezani.saleh Mar 30 '11 at 08:31
  • There is **no way** of doing it without dropping the FKs. Despite [this answer](http://stackoverflow.com/questions/3687575/delete-all-data-in-sql-server-database/3687587#3687587) which indicates that it is possible through disabling referential integrity via `NOCHECK CONSTRAINT ALL` The request to allow this [has been closed as "Wont Fix"](https://connect.microsoft.com/SQLServer/feedback/details/251876/truncate-table-when-fk-are-disable) so not coming any time soon. The answer linked to above does link to an article containing a script that might work by dropping and recreating the FKs though. – Martin Smith Mar 30 '11 at 09:20
  • Give me a solution to reset auto increment of all tables from 0 (when it is empty) – ramezani.saleh Mar 30 '11 at 09:33
2

Why would you want to truncate all tables? If you want an empty database, why not run the CREATE script of the database?

If you want to Truncate a table referenced by a foreign key, you will have to drop the FK constraint first. Disabling constraints is something that is not possible anymore in recent versions of SQL Server.

SchmitzIT
  • 9,227
  • 9
  • 65
  • 92
Wim
  • 1,967
  • 11
  • 19
  • **I am a programmer and i enter many information to the tables for test.Tables have Auto Increment field.And i want to truncate the tables to start their increment from 0 again.** – ramezani.saleh Mar 30 '11 at 09:19
  • **Give me a solution to reset auto increment of all tables from 0 (when it is empty)** – ramezani.saleh Mar 30 '11 at 09:23
  • Best way is to drop the database and create the database with the create database script of your app. This way you also test the create database script of your app. But you can reset all indentity columns in all tables with EXEC sp_MSforeachtable @command1="EXEC DBCC CHECKIDENT ('?')" – Wim Mar 30 '11 at 10:31
0

Reset Auto-Increment? I'm not sure if you understand correctly how this works.

Primary Key incrementing is handled by SQL Server using the IDENTITY specification. If your tables have got no data in them, it will always start from 0.

If I were you, I'd go have a flick through your programming books and pick up some basic database knowledge as it sounds like you're missing some fundamental facts there.

SchmitzIT
  • 9,227
  • 9
  • 65
  • 92
soulfire
  • 9
  • This is not correct. A Primary Key does not necessarily have to be an IDENTITY column. Furthermore, IDENTITY columns don't always start at 0 as you stated. It will start at the specified seed value. – SchmitzIT Oct 26 '12 at 08:15