SQL Server bulk cleanup of all data in the specified database

In practical applications, when we are ready to hand over a project to the customer for use, we need to clear all the tables in the library before the test data to give the customer a clean database, if the table involved a lot, to be emptied, not only time-consuming, but also error-prone and omitted, here I provide a method, Can quickly and efficiently empty the data for all tables in the specified database. For reference only, please exchange different views.

--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)

, 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 for

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 for

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 for

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 beggining of the task ')

DECLARE DataBaseTables2 CURSOR for

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 for

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 in ' + @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;