SqlServer batch cleanup of all data in a specified database

Source: Internet
Author: User

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;

Related Article

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.