Load all tables in database dynamically

    Below script helps in loading all the tables in the database and store that tables in QVDs.

     

    ODBC CONNECT TO MyODBC;

    SQLTableList:

    LOAD "TABLE_NAME" as  TableNames;

     

    SQL SELECT DISTINCT "TABLE_NAME"

    FROM "INFORMATION_SCHEMA".COLUMNS;

     

    Note:

    Information schema.columns is for MS SQL Server database, you can replace that with the euqivalent table in the corresponding database. 


    For oracle ALL_TABLES   ALL_TABLES

    For MYSQL - all_tables or SHOW_TABLES


    How to List All Tables and Describe Tables in Oracle, MySQL, DB2 and PostgreSQL | OneWebSQL


    MySQL :: MySQL 5.7 Reference Manual :: 13.7.5.37 SHOW TABLES Syntax

     

      

    Let vTableCount = NoOfRows('SQLTableList');

    Let vTableCountQVD = 'dbo.[' & NoOfRows('SQLTableList') & ']';

     

    For i = 0 To $(vTableCount) -1

    LET vMyTableName = 'dbo.[' & Peek('TableNames', $(i), 'SQLTableList') & ']';

    LET vMyTableNameQVD = Replace(Peek('TableNames', $(i), 'SQLTableList'), ' ', '');

     

    $(vMyTableNameQVD):

    SQL SELECT *FROM $(vMyTableName);

    STORE $(vMyTableNameQVD) INTO $(ExtractQVD)$(vMyTableNameQVD).QVD;

    DROP Table $(vMyTableNameQVD);

    Next i

     

    Hope this helps others.

     

    Regards,

    Jagan.