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

[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;