SqlServer batch cleanup of all data in a specified database

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;