Cleaning up the database
I have a database with hundreds of tables.
I am creating a script to delete all rows in this database.
Of course, being a relational database, I have to remove rows from children before I can touch the parents.
Is there something I can use to do this, or do I have to do it hard?
EDIT
The accepted answer has been 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 ? '
source to share
You can turn off all restrictions and then delete all data and turn restrictions back on. You can put your code in a stored procedure for reuse. 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
source to share
I am assuming you want a structure without any data?
Can you script use tables / sp / custom functions / triggers / permissions, etc. and then drop the database before recreating it with the script?
This link explains how to create a script for all objects in a database using SQL Server Management Studio ... http://msdn.microsoft.com/en-us/library/ms178078.aspx
source to share
If it was MySQL, I would use " mysqldump --no-data
" to back up the database metadata. Then I completely drop the database and restore the backup with no data.
Apart from the three-step process, this is much faster than transactions and I / O than deleting from each table individually. And it also shrinks the tablespace on disk, which will not be deleted (for InnoDB, that is).
I am not familiar with Microsoft SQL Server backup tools, is there an equivalent option?
I think I found something promising: How To: Generate a Script (SQL Server Management Studio)
To create a Script of the whole database
- In Object Explorer, connect to a SQL Server Engine database instance and then deploy that instance.
- Expand Databases , right-click any database, select Tasks , point to Generate Scripts , and then follow the steps in the Create Scripts Wizard .
source to share