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 ?' 

• user1822: This will only work if all foreign keys are removed before that.
• Karl: Removing foreign keys will work, so will either starting at the child end of each branch of parent-child or grandparent-parent-child relationship. Or run the above multiple time until there is no data left. This approximates the second option I listed above
• user1822: @Karl: "working up" the dependencies does not work. TRUNCATE is not supported on tables having an incoming foreign key. Even if there are no rows referencing the 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";