RSS

Remove all data from the database

30 Aug

SET NOCOUNT ON
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 + ‘]
 ‘);
 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 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;

 
 

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s