Load all tables in database dynamically

[jagan]

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.

[jagan]

HI,

SQL SELECT DISTINCT "TABLE_NAME"

FROM "INFORMATION_SCHEMA".COLUMNS;

This statement will return all table names in the SQL Server details.

Regards,

Jagan.

[#3]

Hi,

Please share here

[#4]

tried with Tera Data not working

[jagan]

Hi,

You just need to change this portion of script

SQL SELECT DISTINCT "TABLE_NAME"

FROM "INFORMATION_SCHEMA".COLUMNS;

Replace with

SQL SELECT DISTINCT TableName FROM DBC.Columns;

Table or view that contains the NumberOfRows & NumberOfColumns? | Teradata Developer Exchange

If this query works then qlikview pull the data from the database.  Here we are just pulling the table names in the database.

Regards,

jagan.

[msteedle]

Instead of trying to rewrite this to be compatible with each database, you can use the SQLTABLES script command to get a list of tables from pretty much any database.

For the person who asked about getting the field names, as well, the SQLCOLUMNS command does that.

[rainerkiru]

SQL-Server:

If you have BLOB (Pictures, documents) columns in the database it slows down your database read speed. To avoid this you need a dynamic sql script with only the needed columns. For doing this i use the following script:

SqlServerSchema:

NoConcatenate load *;

SQL

SELECT

    *

FROM

    (

        SELECT

            tbl.name                AS TabellenName,

            clmns.name              AS [SpaltenName],

            usrt.name               AS [DatenTyp],

            ISNULL(baset.name, N'') AS [SystemType],

            CAST(

                CASE

                    WHEN baset.name IN (N'nchar',

                                        N'nvarchar')

                    AND clmns.max_length <> -1

                    THEN clmns.max_length/2

                    ELSE clmns.max_length

                END AS INT)              AS [LENGTH],

            CAST(clmns.precision AS INT) AS [NumericPrecision]

        FROM

            sys.tables AS tbl WITH (NOLOCK)

        INNER JOIN

            sys.all_columns AS clmns WITH (NOLOCK)

        ON

            clmns.object_id=tbl.object_id

        LEFT OUTER JOIN

            sys.types AS usrt WITH (NOLOCK)

        ON

            usrt.user_type_id = clmns.user_type_id

        LEFT OUTER JOIN

            sys.types AS baset WITH (NOLOCK)

        ON

            baset.user_type_id = clmns.system_type_id

        AND baset.user_type_id = baset.system_type_id

        WHERE

            (

                tbl.name=N'$(vSQLColumnsTableName)'

            AND SCHEMA_NAME(tbl.schema_id)=N'dbo') ) innen

WHERE

   LENGTH <> -1 ;

//Build the load statement and fill variable with this statement;

let _SQLSelectString='';   

LET RCount=NoOfRows('SqlServerSchema');

if '$(RCount)' > '0' then

    LET REndeCount = $(RCount)-1;

    FOR z=0 TO $(REndeCount)

        if trim('$(_SQLSelectStringQualifier)') = '' then

           let Feld='"' & Peek('SpaltenName',z,'SqlServerSchema') & '"';

        else

           let Feld='$(_SQLSelectStringQualifier)."' & Peek('SpaltenName',z,'SqlServerSchema') & '"';

        END IF

        LET _SQLSelectString = '$(_SQLSelectString) $(Feld)';   

        if $(z) = $(REndeCount) then

        else

            LET _SQLSelectString = '$(_SQLSelectString),';   

        END IF

    NEXT   

END IF   

LET _SQLSelectString = 'select ' & '$(_SQLSelectString)';

drop table SqlServerSchema;

[#8]

Hi , I want to  save  oracle tables in qvd , I can read and load table names in dba_tables and I can see that in field in qlik environment but when I try to save table names in variable  vMyTableName , Null is entered , I write a simple scrip for debugging my script as below:

OLEDB CONNECT TO [Provider=OraOLEDB.Oracle.1;Persist Security Info=True;User ID=etc6;Data Source=192.168.1.6/orcl;Extended Properties=""] (XPassword is bRFZfYIGTBNKDbIA);

OracleDatabaseName:

select table_name ,tablespace_name from dba_tables ;

LET vMyTableName =Peek('table_name', 1, 'OracleDatabaseName');

but vMyTableName has NULL value

I trace  in debug mode and  see vMyTableName  can not fill with table name

I try without label  "OracleDatabaseName:"  , result was same

I try with label  dba_tables with or without cote , result was same

I try without cote " ' " around the field_name , result was same

anybody can help me

[jagan]

Hi,

This is a working code, it should work.

Use below condition, I used $(i), you used 1, not sure your database has more than 1 table.  1 in Peek() means second table, try with 0.

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

Regards,

Jagan.

[Anonymous]

Hi Jagan,

Do you know can we do this with cloudera Impala ODBC driver? , My database is Impala....,Can you share any code if this is possible please

Thanks

[rwunderlich]

You can do this with the database agnostic script statement:

SQLTABLES;

That should work for any database driver.