-- 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 @ AllRelationshi
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; |
