Script for Storing/ Dropping Table with Exceptional list

    Problem Statement:

    Always tedious task to maintain drop table statement for all existing table of any Application.

    Below script handle table list which should be there even if there is drop statement. At the end of script it will create dropped table list to track it.

    For Example;

    // Table T1
    T1:
    Load
    RowNo() as ID,
    RowNo()*RowNo() as MeasureT1
    AutoGenerate(24);

     

    // Table T2
    T2:
    Load
    RowNo() as ID,
    RowNo()*RowNo() as MeasureT2
    AutoGenerate(24);

     

    // Table T3
    T3:
    Load
    RowNo() as ID,
    RowNo()*RowNo() as MeasureT3
    AutoGenerate(24);

     

    Below is the script for creating exception list. You can add your own exception list.

    //List of Exceptional Data table which should be omitted from table storing/dropping script
    TableExceptionList:
    Load
    Concat(chr(39)&ExceptionList&chr(39),',') as ExceptionList;
    Load * Inline
    [
    ExceptionList
    TableExceptionList
    TableDetails
    T1
    ]
    ;

    Let vExceptionList = Peek('ExceptionList',0,'TableExceptionList');



     

    Get the No Of Tables from the application


    // No of Table in Applications
    Let vNoOfTables = NoOfTables();
    Trace $(vNoOfTables);

    //Removal of Table
    Let i = $(vNoOfTables);
    do while i > 0
    Let i = $(i)  - 1 ;
    Let vTableName = TableName($(i));

    //Check in Exception List
         vtableException = WildMatch('$(vTableName)',$(vExceptionList));
    if $(vtableException) = 0 then
    //Make Entry of Store/Dropped Table
                    TableDetails:
    Load '$(i)' as TableId, '$(vTableName)' as Table autogenerate 1;              

    //Storing a table into QVD
                    Store $(vTableName) into $(vQVDPath)\$(vTableName).qvd(qvd);
    //Dropping a table
                    Drop table $(vTableName)

    Trace Stored & Dropped Table $(vTableName);
    ENDIF
    loop;

     

    Always Welcome, if any suggestion.