CODEWITHSUNDEEP
sql-serversql-server-2005
ramezani.saleh
ramezani.saleh

Reputation: 571

Truncate all tables of a database in SQL Server 2005

How can I truncate all tables of a database?

Upvotes: 0

Views: 5845

Answers (4)

Boycs
Boycs

Reputation: 5668

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

Upvotes: 2

soulfire
soulfire

Reputation: 9

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.

Upvotes: 0

Wim
Wim

Reputation: 1985

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.

Upvotes: 2

Michaël
Michaël

Reputation: 6734

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

Upvotes: 2

Related Questions