Deleting Sage X3 folder from database

Dear Fellows,

As you may all know that when you delete a folder in X3 it will not be deleted from database with all its references which will eventually increase your database size, and therefore i created an sql procedure that safely delete the folder from database and would like to share it with you.

Sage X3 delete folder.docx  

Parents
  • Thanks Sahem, What line do you specify the schema on?

  • You are most welcome dear, could you please explain your question? as the schema name is used in more than one place.

  • As you know each folder creates a schema within the SQL database. i.e. SEED, X3, Company name etc. However,  within your code, there is nowhere to state which folder schema you are deleting. 

    I can see some @FolderName, [Name] lines, and some ' + Schema_Name' entries, but there is nothing here that implicitly declares the actual folder schema you want to remove.

  • Oooh i see now what you mean, will actually the schema name is always the same as the foldername so I use the folder name as the schema name.

  • So I take it I need to change this:

     SELECT @SQL =  

     'SELECT

          CASE WHEN SO.type=''PK'' THEN '' ALTER TABLE ''+SCHEMA_NAME(SO.schema_id)+''.''+OBJECT_NAME(SO.parent_object_id)+'' DROP CONSTRAINT ''+ SO.name

               WHEN SO.type=''U'' THEN '' DROP TABLE ''+SCHEMA_NAME(SO.schema_id)+''.''+ SO.[Name]

               WHEN SO.type=''V'' THEN '' DROP VIEW  ''+SCHEMA_NAME(SO.schema_id)+''.''+ SO.[Name]

               WHEN SO.type=''P'' THEN '' DROP PROCEDURE  ''+SCHEMA_NAME(SO.schema_id)+''.''+ SO.[Name]         

               WHEN SO.type=''TR'' THEN ''  DROP TRIGGER  ''+SCHEMA_NAME(SO.schema_id)+''.''+ SO.[Name]

               WHEN SO.type  IN (''FN'', ''TF'',''IF'',''FS'',''FT'') THEN '' DROP FUNCTION  ''+SCHEMA_NAME(SO.schema_id)+''.''+ SO.[Name]

                       WHEN SO.type=''SO'' THEN ''  DROP SEQUENCE  ''+SCHEMA_NAME(SO.schema_id)+''.''+ SO.[Name]

           END

    The SO.[Name] to be the schema name, as in SO.SEED

Reply
  • So I take it I need to change this:

     SELECT @SQL =  

     'SELECT

          CASE WHEN SO.type=''PK'' THEN '' ALTER TABLE ''+SCHEMA_NAME(SO.schema_id)+''.''+OBJECT_NAME(SO.parent_object_id)+'' DROP CONSTRAINT ''+ SO.name

               WHEN SO.type=''U'' THEN '' DROP TABLE ''+SCHEMA_NAME(SO.schema_id)+''.''+ SO.[Name]

               WHEN SO.type=''V'' THEN '' DROP VIEW  ''+SCHEMA_NAME(SO.schema_id)+''.''+ SO.[Name]

               WHEN SO.type=''P'' THEN '' DROP PROCEDURE  ''+SCHEMA_NAME(SO.schema_id)+''.''+ SO.[Name]         

               WHEN SO.type=''TR'' THEN ''  DROP TRIGGER  ''+SCHEMA_NAME(SO.schema_id)+''.''+ SO.[Name]

               WHEN SO.type  IN (''FN'', ''TF'',''IF'',''FS'',''FT'') THEN '' DROP FUNCTION  ''+SCHEMA_NAME(SO.schema_id)+''.''+ SO.[Name]

                       WHEN SO.type=''SO'' THEN ''  DROP SEQUENCE  ''+SCHEMA_NAME(SO.schema_id)+''.''+ SO.[Name]

           END

    The SO.[Name] to be the schema name, as in SO.SEED

Children