SQL Script to clear database tables.

Question

I have a need to take backup of a SQL Server Db with tons of data in it and import into another environment for updating and testing. Since, i am not interested in the data, i just want to recreate the schema on my other server. There is an option called 'Generate Script', but is throwing errors running them on the target server.

Curious, if anyone attempted to write a SQL script that would run through all the tables in the db and clear the rows, thereby i could just create the schema backup as .bak file and restore it into another server.

Answer 1

Red Gate produce a product called Sql Compare that you can use to synchronize your schema from one Sql Server database to another. This is probably a lot easier than writing some scripts yourself, plus it allows you to transfer changes easily if the two databases later get out of sync. It's not a free product, but you can use it for free in the 14 day trial.

Answer 2

You need to:

1. Disable every constraints on the tables
2. Truncate tables
3. Recreate constraints

Answer 3

be careful with it, but this does it. it empties all 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'