Step 4 – SQL Server database cleanup

1. Launch SQL Server Management Studio from any location where you can open a session of your SQL Server instance.
2. Connect to your instance with a sysadmin-enabled account. You can also use a Windows or an SQL Server authentication.
3. Verify that you know the name of the database used for this Sage X3 solution. You can check that in your Sage X3 Console configuration.
   
4. Open a new query. Then copy and paste the following SQL Script.
   SQL Script to copy

   /* SAGE ERP X3 folder data cleanup script */
   			/* Author : GBE */
   			/* Set the FOLDER_NAME with the name of the folder to be deleted */
   			/* Sep 2014 : sequence cleanup */
   			/* Aug 2018 : add 'use db_name' (BLE) and explanations */
   
   			DECLARE @FOLDER_NAME nvarchar(30),@sql NVARCHAR(150),@tbl NVARCHAR(100),@sqldrop NVARCHAR(70);
   
   			/* Replace 2nd FOLDER_NAME by the name of your FOLDER */
   			SET @FOLDER_NAME = 'FOLDER_NAME'
   
   			/* Replace db_name by the name of you SQL database */
   			use db_name
   
   			/* Drop all views */
   			while
   			(SELECT count(TABLE_NAME) from INFORMATION_SCHEMA.TABLES where TABLE_SCHEMA = @FOLDER_NAME and TABLE_TYPE='VIEW') > 0
   			begin
   			SELECT top 1 @tbl = TABLE_NAME from INFORMATION_SCHEMA.TABLES where TABLE_SCHEMA = @FOLDER_NAME and TABLE_TYPE='VIEW'
   			SET @sql = 'drop view ['+@FOLDER_NAME+'].['+@tbl+']'
   			exec sp_executesql @sql
   			end
   
   			/* Drop all tables */
   			while
   			(SELECT count(TABLE_NAME) from INFORMATION_SCHEMA.TABLES where TABLE_SCHEMA = @FOLDER_NAME and TABLE_TYPE='BASE TABLE') > 0
   			begin
   			SELECT top 1 @tbl = TABLE_NAME from INFORMATION_SCHEMA.TABLES where TABLE_SCHEMA = @FOLDER_NAME and TABLE_TYPE='BASE TABLE'
   			SET @sql = 'drop table ['+@FOLDER_NAME+'].['+@tbl+']'
   			exec sp_executesql @sql
   			end
   
   			/* drop all sequences */
   			while
   			(SELECT count(SEQUENCE_NAME) from INFORMATION_SCHEMA.SEQUENCES where SEQUENCE_SCHEMA = @FOLDER_NAME ) > 0
   			begin
   			SELECT top 1 @tbl = SEQUENCE_NAME from INFORMATION_SCHEMA.SEQUENCES where SEQUENCE_SCHEMA = @FOLDER_NAME 
   			SET @sql = 'drop sequence ['+@FOLDER_NAME+'].['+@tbl+']'
   			exec sp_executesql @sql
   			end
   
   			set @sqldrop = 'DROP USER ['+@FOLDER_NAME+']'
   			exec sp_executesql @sqldrop
   			set @sqldrop = 'DROP USER ['+@FOLDER_NAME+'_REPORT]'
   			exec sp_executesql @sqldrop
   			set @sqldrop = 'DROP SCHEMA ['+@FOLDER_NAME+']'
   			exec sp_executesql @sqldrop
   			set @sqldrop = 'DROP ROLE ['+@FOLDER_NAME+'_ADX]'
   			exec sp_executesql @sqldrop
   			set @sqldrop = 'DROP ROLE ['+@FOLDER_NAME+'_ADX_H]'
   			exec sp_executesql @sqldrop
   			set @sqldrop = 'DROP ROLE ['+@FOLDER_NAME+'_ADX_R]'
   			exec sp_executesql @sqldrop
   			set @sqldrop = 'DROP ROLE ['+@FOLDER_NAME+'_ADX_RH]'
   			exec sp_executesql @sqldrop
   			set @sqldrop = 'DROP LOGIN ['+@FOLDER_NAME+']'
   			exec sp_executesql @sqldrop
   			set @sqldrop = 'DROP LOGIN ['+@FOLDER_NAME+'_REPORT]'
   			exec sp_executesql @sqldrop
   		GO

5. Replace the second FOLDER_NAME by your folder’s name and the db_name by the name of your database like in the screenshot beneath.
   
6. Then launch the request by clicking Execute or hitting F5.
   This takes some time and might use quite a lot of CPU and I/Os to clean up.
7. At the end it should display the Commands completed successfully message like below:
   
8. You can now close the query manager.