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

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

Answer 2

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 3

If this were MySQL, I would use "mysqldump --no-data" to make a backup of the database metadata only. Then I would drop the database entirely and restore my data-less backup.

In addition to being a three-step process, it is a lot faster just in terms of transactions and I/O than deleting from each table individually. And it also shrinks the tablespace on disk, which deleting would not do (for InnoDB, that is).

I'm not familiar with Microsoft SQL Server's backup tools, is there some equivalent option?

---

I think I've found something promising: How to: Generate a Script (SQL Server Management Studio)

To generate a script of an entire database

1. In Object Explorer, connect to an instance of the SQL Server Database Engine and then expand that instance.
2. Expand Databases, right-click any database, point to Tasks, point to Generate Scripts, and then follow the steps in the Generate Scripts Wizard.