List of Tables

[#1]

Hi,

maybe someone of you can help me.

We have a database where I take Datas from.

We take this datafields out of tables from the database.

Now my question:

I dont Need datas from a table but a list of all tables within the database.

How can I realise that?

Our IT-Guy wrote to me the following, but unfortunately qlikview cannot handle this:

select

substr(segment_name,1,30) segment,

substr(segment_type,1,10) typ,

bytes/1024/1024 MB

from dba_segments

where segment_type = ‘TABLE’

Thanks in advance

Chris

[MK_QSL]

Check this...

Load all tables in database dynamically

[Gysbert_Wassenaar]

Qlikview can send that same SQL statement to the database server. Provided you connect with the correct credentials the database server will execute that statement and return the results to Qlikview. Qlikview doesn't do anything with/to the SQL apart from sending it to the database server for execution. If it doesn't work then either your dba made an error or the credentials you use to connect to the database don't have enough priviliges the execute the sql statement. Ask your dba for help in that case.

[Peter_Cammaert]

If this works for your database (what RDBMS type is this?) you can submit it in exactly the same way by opening a connection to the database and submitting the same statement with 'SQL' in front (and terminated with a semicolon)

Peter

[flipside]

Check out the SQLTABLES and SQLCOLUMNS commands in Qlikview help, they should give you all the tables in the database you have connected to.

flipside

[Kushal_Chawda]

Which database you are using?

[#7]

Hi Kush141087,

we are using Oracle Database 9.

Chris

[#8]

Hi Flipside,

I tried your Suggestion and it works fine.

I just Need to Filter to some Special tables, but this unfortunatley dont work yet.

Can you see the mistake?

sqltables
WHERE TABLE_SCHEMA = 'CODA'
AND TABLE_TYPE = 'Table'
AND TABLE_NAME like 'EFA'

Thanks in advance

Chris

[flipside]

These commands can be a little tricky to get right. It should also be noted that the fields & data returned may be different even between different connection providers (i.e. ODBC or OLEDB) to the same database.

From memory, you might need to do a preceding load something like ...

**connection string **

Load *

where TABLE_SCHEMA ='CODA' AND TABLE_TYPE='Table' AND Wildmatch(TABLE_NAME,'*EFA*');

sqltables;

flipside

edit: No this doesn't work, my memory is obviously not as good as I thought! Use Gysbert's method.

Loading SQLTABLES into a named table might show as an error but will still run. You can do a preceding statement Load *; to get rid of the error red underline, so ...

TableName:

Load *;

SQLTABLES;

[flipside]

You might need to dump the table to a txt file and read back in. I'm sure there's a way to avoid this but don't have it to hand  ...

Table1:

Load *;
SQLTABLES;

store Table1 into Table1.txt (txt);

DROP Table Table1;

Table2:
LOAD * FROM [Table1.txt] (txt, utf8, embedded labels, delimiter is ',', msq)
where TABLE_TYPE='TABLE';

flipside