How to load all tables in a database in oracle?

[#1]

Hello, I wonder if there is a possibility to load all tables by owner, showing the names of fields in tables.

Thank you.

[maxgro]

yes

this is for sql server

Load all tables in database dynamically

for oracle replace the "table of table" table with all_tables or dba_tables and filter by owner

if you want to make the select statement wth the fields of every table, use all_tab_columns or dba_...

[#3]

Thanks for your answer,

i have a question, what is the "table of table" table? Sorry i'm new in Qlikview world.

[buzzy996]

if i'm not wrong,that's oracle syntax to fetch all table names.

[maxgro · Accepted solution]

in the link I posted there is a table

"INFORMATION_SCHEMA".COLUMNS

with a fields containing the name of the tables

The link is for sql server. In an Oracle database you have to replace INFORMATION_SCHEMA with all_tables because in Oracle table all_tables you can find all the tables you can view in the Oracle database

the script is, I have just tried now on Oracle

ODBC CONNECT32 TO [xxxxx;DBQ=xxx] (XUserId is xxxxx, XPassword is xxxx);

OracleTableList:

SQL

select TABLE_NAME, OWNER from all_tables

where owner='DWHCONT';     // change the owner/schema

Let vTableCount = NoOfRows('OracleTableList');

For i = 0 To $(vTableCount) -1

     LET vMyTableName = Peek('OWNER', $(i), 'OracleTableList') & '.' & Peek('TABLE_NAME', $(i), 'OracleTableList');

     LET vMyTableNameQVD = Replace(Peek('TABLE_NAME', $(i), 'OracleTableList'), ' ', '_');

     $(vMyTableNameQVD):

     SQL SELECT * FROM $(vMyTableName) ;

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

     DROP Table $(vMyTableNameQVD);

Next i;

[maxgro]

another one is SQLTABLES, this is from Qlikview help

The sqltables statement returns a set of fields describing the tables of an ODBC data source, to which a connect has been made. These fields can be combined with the fields generated by the SQLColumns and SQLTypes commands in order to give a good overview of a given database. The five standard fields are:

TABLE_QUALIFIER

TABLE_OWNER

TABLE_NAME

TABLE_TYPE

REMARKS

For a detailed description of these fields, see an ODBC reference handbook.

The syntax is:

sqltables

Example:

connect to 'MS Access 7.0 Database; DBQ=C:\Course3\DataSrc\QWT.mbd';

sqltables;

Note!
Some ODBC drivers may not support this command.
Some ODBC drivers may produce additional fields.

[Dmitry-Vereschagin]

It might be a better way to use user_tables instead of all_tables Oracle system view.
Then the procedure looks like 

Let owner='DWHCONT';
SQL
select TABLE_NAME from user_tables;
Let vTableCount = NoOfRows('OracleTableList');
For i = 0 To $(vTableCount) -1
     LET vMyTableName = '$(owner)' & '.' & Peek('TABLE_NAME', $(i), 'OracleTableList');
     LET vMyTableNameQVD = Replace(Peek('TABLE_NAME', $(i), 'OracleTableList'), ' ', '_');
...