SQL Script to clean database tables

Question

I need to backup SQL Server Db with a lot of data in it and import it into another environment for updating and testing. Since I'm not interested in data, I just want to recreate the schema on my other server. There is an option called Create Script, but throws errors on the destination server.

Curiously, if someone tried to write an SQL script that would skip all the tables in db and clear the rows, I could just back up the schema as a .bak file and restore it to another server.

Answer 1

be careful with this, but it does it. it empties all the tables in your database.

-- disable referential integrity
EXEC sp_MSForEachTable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'

-- print table name
EXEC sp_MSForEachTable 'truncate table ?'

-- enable referential integrity again
EXEC sp_MSForEachTable 'ALTER TABLE ? CHECK CONSTRAINT ALL'

Answer 2

Red Gate creates a product called Sql Compare that you can use to synchronize your schema from one Sql server database to another. This is probably much easier than writing the scripts themselves, and it also makes it easy to push changes if the two databases later go out of sync. This is not a free product, but you can use it for free in a 14-day trial.

Answer 3

You need:

• Disable all table restrictions
• Circumcision tables
• Restore Restrictions