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.

[joffremota]

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.

[jagan]

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]

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

Thank you!

Regards,

Joffre Mota

[#5]

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

[#6]

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

---SaiLeela Maguluri

[kshitiz7]

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

[jagan]

Hi,

Check this link

http://stackoverflow.com/questions/201282/how-to-get-table-names-from-access

Regards,

Jagan.

[#9]

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

[jagan]

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.

[#11]

@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