[Recommended] (SqlServer) Batch cleanup of all data in the specified database

Source: Internet
Author: User
[Recommended] (SqlServer) Batch cleanup all data in the specified database to establish a personal brand through knowledge sharing. In practical applications, when we want to hand over a project to the customer, we need to clear all the previous test data of all tables in the database to give the customer a clean database, if a large number of tables are involved, one-to-one clearing is required.

[Recommended] (SqlServer) Batch cleanup all data in the specified database to establish a personal brand through knowledge sharing. In practical applications, when we want to hand over a project to the customer, we need to clear all the previous test data of all tables in the database to give the customer a clean database, if a large number of tables are involved, one-to-one clearing is required.

[Recommendation] (SqlServer)Batch clear all data in a specified database

--Establish a personal brand through knowledge sharing.

In practical applications, when we want to hand over a project to the customer, we need to clear all the previous test data of all tables in the database to give the customer a clean database, if there are many tables involved, it takes not only time to clear them one by one, but also prone to errors and missing/deleted. Here I provide a method, you can quickly and effectively clear data in all tables of a specified database. For your reference only.

-- Remove all data from a database

SET NOCOUNT ON
-- Tables to ignore
DECLARE @ IgnoreTables
TABLE (TableName varchar (512 ))
Insert into @ IgnoreTables (TableName) VALUES ('sysdiagrams ')
DECLARE @ AllRelationships
TABLE (ForeignKey varchar (512)
, TableName varchar (512)
, ColumnName varchar (512)
And ReferenceTableName varchar (512)
, ReferenceColumnName varchar (512)
. DeleteRule varchar (512 ))
Insert into @ AllRelationships
SELECT f. name AS ForeignKey,
OBJECT_NAME (f. parent_object_id) AS TableName,
COL_NAME (fc. parent_object_id,
Fc. parent_column_id) AS ColumnName,
OBJECT_NAME (f. referenced_object_id) AS ReferenceTableName,
COL_NAME (fc. referenced_object_id,
Fc. referenced_column_id) AS ReferenceColumnName,
Delete_referential_action_desc as DeleteRule
FROM sys. foreign_keys AS f
Inner join sys. foreign_key_columns AS fc
ON f. OBJECT_ID = fc. constraint_object_id


DECLARE @ TableOwner varchar (512)
DECLARE @ TableName varchar (512)
DECLARE @ ForeignKey varchar (512)
DECLARE @ ColumnName varchar (512)
DECLARE @ ReferenceTableName varchar (512)
DECLARE @ ReferenceColumnName varchar (512)
DECLARE @ DeleteRule varchar (512)


PRINT ('loop through all tables and switch all constraints to have a delete rule of CASCADE ')
DECLARE DataBaseTables0
CURSOR
SELECT SCHEMA_NAME (t. schema_id) AS schema_name, t. name AS table_name
FROM sys. tables AS t;

OPEN DataBaseTables0;

Fetch next from DataBaseTables0
INTO @ TableOwner, @ TableName;

WHILE @ FETCH_STATUS = 0
BEGIN
IF (not exists (select top 1 1 FROM @ IgnoreTables WHERE TableName = @ TableName ))
BEGIN
PRINT '[' + @ TableOwner + ']. [' + @ TableName + ']';

DECLARE DataBaseTableRelationships CURSOR
SELECT ForeignKey, ColumnName, ReferenceTableName, ReferenceColumnName
FROM @ AllRelationships
WHERE TableName = @ TableName

OPEN DataBaseTableRelationships;
Fetch next from DataBaseTableRelationships INTO @ ForeignKey, @ ColumnName, @ ReferenceTableName, @ ReferenceColumnName;

IF @ FETCH_STATUS <> 0
PRINT '===> No Relationships ';

WHILE @ FETCH_STATUS = 0
BEGIN
PRINT '===> switching delete rule on' + @ ForeignKey + 'to CASCADE ';
BEGIN TRANSACTION
BEGIN TRY
EXEC ('

Alter table ['+ @ TableOwner +']. ['+ @ TableName +']
Drop constraint '+ @ ForeignKey + ';

Alter table ['+ @ TableOwner +']. ['+ @ TableName +'] ADD CONSTRAINT
'+ @ ForeignKey +' FOREIGN KEY
(
'+ @ ColumnName +'
) REFERENCES '+ @ ReferenceTableName +'
(
'+ @ ReferenceColumnName +'
) On delete cascade;
');
COMMIT TRANSACTION
END TRY
BEGIN CATCH
PRINT '=> can't switch' + @ ForeignKey + 'to CASCADE,-' +
CAST (ERROR_NUMBER () as varchar) + '-' + ERROR_MESSAGE ();
ROLLBACK TRANSACTION
End catch;

Fetch next from DataBaseTableRelationships INTO @ ForeignKey, @ ColumnName, @ ReferenceTableName, @ ReferenceColumnName;
END;

CLOSE DataBaseTableRelationships;
DEALLOCATE DataBaseTableRelationships;

END
PRINT '';
PRINT '';

Fetch next from DataBaseTables0
INTO @ TableOwner, @ TableName;
END
CLOSE DataBaseTables0;
DEALLOCATE DataBaseTables0;

PRINT ('loop though each table and DELETE All data from the table ')

DECLARE DataBaseTables1 CURSOR
SELECT SCHEMA_NAME (t. schema_id) AS schema_name, t. name AS table_name
FROM sys. tables AS t;

OPEN DataBaseTables1;

Fetch next from DataBaseTables1
INTO @ TableOwner, @ TableName;

WHILE @ FETCH_STATUS = 0
BEGIN
IF (not exists (select top 1 1 FROM @ IgnoreTables WHERE TableName = @ TableName ))
BEGIN
PRINT '[' + @ TableOwner + ']. [' + @ TableName + ']';
PRINT '=> deleting data from [' + @ TableOwner + ']. [' + @ TableName + ']';
BEGIN TRY
EXEC ('
Delete from ['+ @ TableOwner +']. ['+ @ TableName +']
Dbcc checkident (['+ @ TableName +'], RESEED, 0)
');
END TRY
BEGIN CATCH
PRINT '=> can't FROM [' + @ TableOwner + ']. [' + @ TableName + '],-' +
CAST (ERROR_NUMBER () as varchar) + '-' + ERROR_MESSAGE ();
End catch;
END

PRINT '';
PRINT '';

Fetch next from DataBaseTables1
INTO @ TableOwner, @ TableName;
END
CLOSE DataBaseTables1;
DEALLOCATE DataBaseTables1;

PRINT ('loop through all tables and switch all constraints to have a delete rule they had at the gingining of the task ')

DECLARE DataBaseTables2 CURSOR
SELECT SCHEMA_NAME (t. schema_id) AS schema_name, t. name AS table_name
FROM sys. tables AS t;
OPEN DataBaseTables2;

Fetch next from DataBaseTables2
INTO @ TableOwner, @ TableName;

WHILE @ FETCH_STATUS = 0
BEGIN

IF (not exists (select top 1 1 FROM @ IgnoreTables WHERE TableName = @ TableName ))
BEGIN
PRINT '[' + @ TableOwner + ']. [' + @ TableName + ']';

DECLARE DataBaseTableRelationships CURSOR
SELECT ForeignKey, ColumnName, ReferenceTableName, ReferenceColumnName, DeleteRule
FROM @ AllRelationships
WHERE TableName = @ TableName

OPEN DataBaseTableRelationships;
Fetch next from DataBaseTableRelationships INTO @ ForeignKey, @ ColumnName, @ ReferenceTableName, @ ReferenceColumnName, @ DeleteRule;

IF @ FETCH_STATUS <> 0
PRINT '===> No Relationships ';

WHILE @ FETCH_STATUS = 0
BEGIN
DECLARE @ switchBackTo varchar (50) =
CASE
WHEN @ DeleteRule = 'no _ action' THEN 'No Action'
WHEN @ DeleteRule = 'cascade 'then 'cascade'
WHEN @ DeleteRule = 'set _ null' THEN 'set null'
WHEN @ DeleteRule = 'set _ default' THEN 'set default'
END

PRINT '===> switching delete rule on' + @ ForeignKey + 'to' + @ switchBackTo;

BEGIN TRANSACTION
BEGIN TRY
EXEC ('

Alter table ['+ @ TableOwner +']. ['+ @ TableName +']
Drop constraint '+ @ ForeignKey + ';

Alter table ['+ @ TableOwner +']. ['+ @ TableName +'] ADD CONSTRAINT
'+ @ ForeignKey +' FOREIGN KEY
(
'+ @ ColumnName +'
) REFERENCES '+ @ ReferenceTableName +'
(
'+ @ ReferenceColumnName +'
) On delete '+ @ switchBackTo +'
');

COMMIT TRANSACTION
END TRY
BEGIN CATCH
PRINT '=> can't change' + @ ForeignKey + 'back to '+ @ switchBackTo +',-'+
CAST (ERROR_NUMBER () as varchar) + '-' + ERROR_MESSAGE ();
ROLLBACK TRANSACTION
End catch;

Fetch next from DataBaseTableRelationships
INTO @ ForeignKey, @ ColumnName, @ ReferenceTableName, @ ReferenceColumnName, @ DeleteRule;
END;

CLOSE DataBaseTableRelationships;
DEALLOCATE DataBaseTableRelationships;

END
PRINT '';
PRINT '';

Fetch next from DataBaseTables2
INTO @ TableOwner, @ TableName;
END
CLOSE DataBaseTables2;

DEALLOCATE DataBaseTables2;

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.