LAE

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: dhrobertson

Hi all,

I can connect to a database with the DB Query node with no issue. my problem is I then have to know all the tables I want to access rather than being able to browse the various table names like in other applications.
does anyone know any generic tool/explorer that I could use to view all the tables in a given database to then work out which tables I need to reference in my SQL query in Lavastorm. (can someone also tell me why a little database explorer is not built into lavastorm at all to facilitate this?)

if there isn't a tool of some sorts ( I don't want to have to install SQL admin studios or oracle equivalent) to do this then what methods do you guys generally use to view what tables are available to attach too without having to ask a DBAdmin about the structure of the database?

thanks

doug

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: stonysmith

The query below will retrieve all the table and column names from an Oracle database.
There is a similar type of query you can run for other database vendors.

select at.owner,atc.TABLE_NAME,
atc.COLUMN_NAME,
atc.DATA_TYPE,
atc.DATA_LENGTH,
atc.DATA_PRECISION,
atc.DATA_SCALE,
atc.NULLABLE
from sys.all_tables at
left outer join sys.all_tab_columns atc
on at.table_name = atc.table_name

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: dhrobertson

Thanks, sorry am not that familiar with working with DB's so was not aware of being able to query the schema like that.
an equivalent that works for me in SQL was
SELECT * from information_schema.columns

this listed all tables as well as columns along with datatypes.

thanks for the help and the Oracle query which I will also need later....