Riverbird cleanup script for a c-entron database

Truncates the specified and found tables, but does not delete the tables themselves.
The only exception is the table

Asset Management Article Assignment

This includes the RMM references for contract billing, which must not be truncated.

Please note: to actually write the changes and not just perform a test run, comment out or delete "rollback tran" at the end of the statement and activate "commit tran" instead:

commit tran -- rollback tran

The full statement can be found here

/* ATTENTION - The script runs in a transaction # INFO If the script is executed in this state, a test run is initiated and at the end the database is completely reset to its previous state with the command rollback tran, i.e. no changes are made # 2DO If the test run is successful, comment out the rollback at the END of this statement: -- rollback tran and, in return, activate "commit tran" by deleting the two "--" before "commit tran". Execute the statement again after this change to write the changes*/begin tran-- Check whether the procedure already exists and then change it

IF OBJECT_ID('TruncateRiverTablesWithPrefixes', 'P') IS NOT NULL DROP PROCEDURE TruncateRiverTablesWithPrefixes;GOCREATE PROCEDURE TruncateRiverTablesWithPrefixesASBEGIN DECLARE @table NVARCHAR(256); DECLARE @foreignKey NVARCHAR(256); DECLARE @dropForeignKeySQL NVARCHAR(MAX); DECLARE @truncateSQL NVARCHAR(MAX); -- Step 1: Dynamically find all relevant tables PRINT 'Phase 1: Finding tables with the specified prefixes...'; DECLARE @tableList TABLE (tableName NVARCHAR(256)); -- Insert all tables with the specified prefixes into a temporary list INSERT INTO @tableList (tableName) SELECT name FROM sys.tables WHERE 1 = 1 and ((name LIKE 'AssetManagement%') OR (name LIKE 'DocumentationWizard%') OR (name LIKE 'DocuWizard%') OR (name LIKE 'Mon%') OR (name LIKE 'Deployable%') OR (name LIKE 'Remote%')) OR (name LIKE 'RBSync%') OR (name LIKE 'DocumentationPageOldVersions') OR (name LIKE 'PatchManagementDeployable%') OR (name LIKE 'RiverbirdAgentDeployment%') and name <> 'AssetManagementArticleAssignment'; -- Step 2: Delete all foreign keys PRINT 'Phase 2: Deleting all foreign keys for the found tables...'; DECLARE table_cursor CURSOR FOR SELECT tableName FROM @tableList; OPEN table_cursor; FETCH NEXT FROM table_cursor INTO @table; WHILE @@FETCH_STATUS = 0 BEGIN -- Inner cursor to find foreign keys that refer to the current table DECLARE foreignKey_cursor CURSOR FOR SELECT fk.name AS foreignKeyName FROM sys.foreign_keys AS fk WHERE OBJECT_NAME(fk.parent_object_id) = @table; OPEN foreignKey_cursor; FETCH NEXT FROM foreignKey_cursor INTO @foreignKey; -- Remove all foreign keys that refer to the table WHILE @@FETCH_STATUS = 0 BEGIN -- Dynamically delete the foreign key SET @dropForeignKeySQL = 'ALTER TABLE ' + @table + ' DROP CONSTRAINT ' + @foreignKey; PRINT 'Executing: ' + @dropForeignKeySQL; -- Display the SQL command to be executed EXEC sp_executesql @dropForeignKeySQL; FETCH NEXT FROM foreignKey_cursor INTO @foreignKey; END; CLOSE foreignKey_cursor; DEALLOCATE foreignKey_cursor; FETCH NEXT FROM table_cursor INTO @table; END; -- Close cursor and release resources CLOSE table_cursor; DEALLOCATE table_cursor; -- Step 3: Empty all tables (TRUNCATE TABLE) PRINT 'Phase 3: Truncating all tables...'; -- New cursor for the second pass to empty the tables DECLARE truncate_cursor CURSOR FOR SELECT tableName FROM @tableList order by tableName; OPEN truncate_cursor; FETCH NEXT FROM truncate_cursor INTO @table; WHILE @@FETCH_STATUS = 0 BEGIN -- TRUNCATE TABLE for each table SET @truncateSQL = 'TRUNCATE TABLE ' + @table; PRINT 'Executing: ' + @truncateSQL; -- Display the SQL command to be executed EXEC sp_executesql @truncateSQL; FETCH NEXT FROM truncate_cursor INTO @table; END; -- Close cursor and release resources CLOSE truncate_cursor; DEALLOCATE truncate_cursor; PRINT 'Operation completed successfully.';END; GO -- Execute procedure immediately EXEC TruncateRiverTablesWithPrefixes; -- Drop procedure after execution to clean up the database DROP PROCEDURE TruncateRiverTablesWithPrefixes; GO /* If no errors occur, execute commit tran and disable rollback tran */ -- commit tran rollback tran