Page 1 of 1
How to get all table names on ODBC connection
Posted: Mon Dec 17, 2018 12:09 am
by sk1080in
Hi,
Is there a way to get all table names in Turbo Integrator Process by selecting ODBC as datasource type ? I have few queries already working, however need to explore more tables but donot know the name of other tables.
Thanks you
Re: How to get all table names on ODBC connection
Posted: Mon Dec 17, 2018 12:11 am
by Wim Gielis
Not really. You would be better off logging on to the relational database to which the ODBC is connecting.
Maybe (I'm not an expert of these systems) there might be a table itself, containing the names of the tables.
But then again, even if that's possible, just log on as I mentioned above.
Re: How to get all table names on ODBC connection
Posted: Mon Dec 17, 2018 12:24 am
by sk1080in
Thank you Wim
Re: How to get all table names on ODBC connection
Posted: Mon Dec 17, 2018 5:03 am
by gtonkin
I would also use some client tools to connect to the database but if you only have TI then use it.
I use the following:
SELECT Table_Name FROM INFORMATION_SCHEMA.TABLES
Should return the tables then you can use:
SELECT Column_Name FROM INFORMATION_SCHEMA.COLUMNS Where Table_Name='<tablename>'
Re: How to get all table names on ODBC connection
Posted: Mon Dec 17, 2018 9:54 am
by lotsaram
gtonkin wrote: ↑Mon Dec 17, 2018 5:03 am
I would also use some client tools to connect to the database but if you only have TI then use it.
"Only having TI" is a very rare if ever occurrence. There are numerous lightweight SQL IDEs which don't require install. And in those very locked down environments such as large banks I can't imagine there wouldn't be an approved SQL client either already installed or able to be ordered without much fuss.
The TI query window is hardly a query building environment! Use each tool for what it's good for and develop the query in an appropriate tool and copy/paste into TI. Otherwise you're writing the query blind which is just asking for problems.
The query to run to return a list of table names is going to depend on the type of RDBMS. The query from gtonkin will work for MS SQLServer or Postgres
but for Oracle it would be SELECT DISTINCT OBJECT_NAME FROM USER_OBJECTS WHERE OBJECT_TYPE = 'TABLE'
or for MySQL the much simpler SHOW TABLES
... but a list of tables doesn't get you very far. If you are going to write queries then you need to know the fields and especially the primary keys & foreign keys. For this you really need a tool that is built for the job.
Re: How to get all table names on ODBC connection
Posted: Mon Dec 17, 2018 10:09 pm
by sk1080in
gtonkin wrote: ↑Mon Dec 17, 2018 5:03 am
I would also use some client tools to connect to the database but if you only have TI then use it.
I use the following:
SELECT Table_Name FROM INFORMATION_SCHEMA.TABLES
Should return the tables then you can use:
SELECT Column_Name FROM INFORMATION_SCHEMA.COLUMNS Where Table_Name='<tablename>'
Thank you Gtonkin...It really worked !!!
Re: How to get all table names on ODBC connection
Posted: Mon Dec 17, 2018 10:13 pm
by sk1080in
lotsaram wrote: ↑Mon Dec 17, 2018 9:54 am
gtonkin wrote: ↑Mon Dec 17, 2018 5:03 am
I would also use some client tools to connect to the database but if you only have TI then use it.
"Only having TI" is a very rare if ever occurrence. There are numerous lightweight SQL IDEs which don't require install. And in those very locked down environments such as large banks I can't imagine there wouldn't be an approved SQL client either already installed or able to be ordered without much fuss.
The TI query window is hardly a query building environment! Use each tool for what it's good for and develop the query in an appropriate tool and copy/paste into TI. Otherwise you're writing the query blind which is just asking for problems.
The query to run to return a list of table names is going to depend on the type of RDBMS. The query from gtonkin will work for MS SQLServer or Postgres
but for Oracle it would be SELECT DISTINCT OBJECT_NAME FROM USER_OBJECTS WHERE OBJECT_TYPE = 'TABLE'
or for MySQL the much simpler SHOW TABLES
... but a list of tables doesn't get you very far. If you are going to write queries then you need to know the fields and especially the primary keys & foreign keys. For this you really need a tool that is built for the job.
Thank you lotsaram, Understand your point completely. For me it is a SQL environment which I have access through ODBC only, so no one other knows how to access the tables directly. I was looking for some specific columns and found them in one of the tables. Agreed that writing the query in TI would not be a complete picture.
Re: How to get all table names on ODBC connection
Posted: Mon Dec 17, 2018 11:16 pm
by Wim Gielis
lotsaram wrote: ↑Mon Dec 17, 2018 9:54 am"Only having TI" is a very rare if ever occurrence.
Rare case, but apparently, it exists
