Cleaning up a database

Question

I have a database with hundreds of tables.

I am building a script to delete all of the rows in this database.

Of course, being a relational database, I have to delete rows from the children before I can touch the parents.

Is there something I can use for this or do I have to do this the hard way?

EDIT

Accepted Answer was modified to include Disable Trigger as well

EXEC sp_MSForEachTable 'DISABLE TRIGGER ALL ON ? '
EXEC sp_MSForEachTable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'
EXEC sp_MSForEachTable 'DELETE FROM ?'
EXEC sp_MSForEachTable 'ALTER TABLE ? CHECK CONSTRAINT ALL'
EXEC sp_MSForEachTable 'ENABLE TRIGGER ALL ON ? '

Answer 1

You can disable all the constraints, then delete all the data, and enable the constraints again. You can put the code in a stored procedure for reutilization. Something quick and dirty:

CREATE PROCEDURE sp_EmplyAllTable
AS
EXEC sp_MSForEachTable ‘ALTER TABLE ? NOCHECK CONSTRAINT ALL’
EXEC sp_MSForEachTable ‘DELETE FROM ?’
EXEC sp_MSForEachTable ‘ALTER TABLE ? CHECK CONSTRAINT ALL’
GO

Answer 2

I'm guessing you want the structure without any of the data?

Can you script the tables / sp's / user-defined functions / triggers / permissions etc. and then drop the database before recreating it with the script?

This link explains how to generate a script for all the objects in a database using SQL server Management studio... http://msdn.microsoft.com/en-us/library/ms178078.aspx