Qlik Community

QlikView Documents

Documents for QlikView related information.

Load all tables in database dynamically

MVP & Luminary
MVP & Luminary

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.

Labels (1)
Comments
joffremota
New Contributor III

The compatibility of this script is with which Data Base? I've tried with SQL Server 2008 R2 and Oracle 11g but it didn't work.

0 Likes
MVP & Luminary
MVP & Luminary

Hi,

This works for SQL Server and MySQL.  You need to change below statement according to your database to get the table names in database

SELECT DISTINCT "TABLE_NAME"

FROM "INFORMATION_SCHEMA".COLUMNS;

Check below URL

INFORMATION_SCHEMA.COLUMNS tutorial and example

MySQL :: MySQL 5.0 Reference Manual :: 19.4 The INFORMATION_SCHEMA COLUMNS Table

Regards,

Jagan.

joffremota
New Contributor III

Worked perfectly now on SQL Server 2008. Don't know what I did wrong on the first time.


Thank you!

Regards,

Joffre Mota

0 Likes
Not applicable

Hi Jagan,

I got the list of all the tables in the Database with this script. How can I get the List of all the columns for all the corresponding tables in the Database please?. Thanks Much in advance

Regards,

Saileela Maguluri

0 Likes
Not applicable

Alright! I got it done. will post the script if anyone needs. Cheers

---SaiLeela Maguluri

0 Likes
kshitiz7
New Contributor

how do you do the same on MS access data base.

0 Likes
MVP & Luminary
MVP & Luminary
Not applicable

Hi All,

I need to load 160 tables from sql server 2008 dynamically. it takes time to load each tables manually

i m getting an error

i need all the tables and columns

0 Likes
MVP & Luminary
MVP & Luminary

Hi,

If your script is correct then it will load, can you create a new thread with the error message you are getting.

Regards,

Jagan.

Not applicable

@SaiLeela Maguluri. Please post the script to extract column AND table names if you don't mind!

@Jagan Mohan.

Im confused, could you give me an example of what would go into this statement for an actual MS SQL Database:

SQL SELECT DISTINCT "TABLE_NAME"

FROM "INFORMATION_SCHEMA".COLUMNS;

Regards, Dirk

0 Likes
Version history
Revision #:
1 of 1
Last update:
‎2013-02-27 08:46 AM
Updated by: