Truncate all tables of a database in SQL Server 2005

开发者_Python百科How can I truncate all tables of a database?

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.

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

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

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.