SQL Server: reset all database tables

Question

I have a SQL Server 2008 R2 database with about 60 tables and around 300 columns. I want to reset all the data and auto increments, I found a question somehow similar but the answer there is to recreate database.

Is there any way to keep the database and just reset it?

Answer 1

Recreate is the better option, because:

• You end up with a script to create the db
• It's dirt simple.
• You don't have to take the complexity of your schema in to account.
• SQL Server will be more than happy to create the script for you.

The other option is to write something that looks through the schema, identifies tables with foreign key constraints so they can be dropped, drops them, then finds the identities and reseeds them, then puts the FKs back

Or if you are a masochist, you could write (well try to) something to use the dependency order

Both of options 2 and 3 are high maintenance.

Answer 2

Probably this LINK will help you

EXEC sp_MSForEachTable 'TRUNCATE TABLE ?' 

Answer 3

Execute both queries to reset auto incremented id

string Query1 = @"ALTER TABLE [Your Table] DROP COLUMN id";
string Query2 = @"ALTER TABLE [Your Table] ADD id int IDENTITY";